BUTTON SAVE

Private Sub Button12_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button12.Click
        get_kon()
        Dim simpan As SqlClient.SqlTransaction = kon.BeginTransaction
        Dim command As SqlClient.SqlCommand = kon.CreateCommand
        bersih()
        Try
            With command
                .CommandText = "InputAnalis5C"
                .CommandType = CommandType.StoredProcedure
                .Connection = kon
                .Transaction = simpan
                .Parameters.Add("@NONAS", SqlDbType.NVarChar, 12).Value = TextBox1.Text
                .Parameters.Add("@NAMA", SqlDbType.NVarChar, 75).Value = TextBox2.Text
                .Parameters.Add("@ALAMAT", SqlDbType.NVarChar, 75).Value = TextBox3.Text
                .Parameters.Add("@NOKTP", SqlDbType.NVarChar, 25).Value = TextBox4.Text
                .Parameters.Add("@CHAR1", SqlDbType.NVarChar, 20).Value = TextBox5.Text
                .Parameters.Add("@CHAR2", SqlDbType.NVarChar, 20).Value = TextBox6.Text
                .Parameters.Add("@CHAR3", SqlDbType.NVarChar, 20).Value = TextBox7.Text
                .Parameters.Add("@CHAR4", SqlDbType.NVarChar, 20).Value = TextBox8.Text
                .Parameters.Add("@CHAR5", SqlDbType.NVarChar, 20).Value = TextBox9.Text
                .Parameters.Add("@CHAR6", SqlDbType.NVarChar, 20).Value = TextBox10.Text
                .Parameters.Add("@CHAR7", SqlDbType.NVarChar, 20).Value = TextBox11.Text
                .Parameters.Add("@CHAR8", SqlDbType.NVarChar, 20).Value = TextBox12.Text
                .Parameters.Add("@CHAR9", SqlDbType.NVarChar, 20).Value = TextBox13.Text
                .Parameters.Add("@CHAR10", SqlDbType.NVarChar, 20).Value = TextBox14.Text
                .Parameters.Add("@CHAR11", SqlDbType.NVarChar, 20).Value = TextBox15.Text
                .Parameters.Add("@CHAR12", SqlDbType.NVarChar, 20).Value = TextBox16.Text
                .Parameters.Add("@CAP1", SqlDbType.NVarChar, 20).Value = TextBox17.Text
                .Parameters.Add("@CAP2", SqlDbType.NVarChar, 20).Value = TextBox18.Text
                .Parameters.Add("@CAP3", SqlDbType.NVarChar, 20).Value = TextBox19.Text
                .Parameters.Add("@CAP4", SqlDbType.NVarChar, 20).Value = TextBox20.Text
                .Parameters.Add("@CAP5", SqlDbType.NVarChar, 20).Value = TextBox21.Text
                .Parameters.Add("@CAP6", SqlDbType.NVarChar, 20).Value = TextBox22.Text
                .Parameters.Add("@CAP7", SqlDbType.NVarChar, 20).Value = TextBox23.Text
                .Parameters.Add("@CAP8", SqlDbType.NVarChar, 20).Value = TextBox24.Text
                .Parameters.Add("@CAP9", SqlDbType.NVarChar, 20).Value = TextBox25.Text
                .Parameters.Add("@CAP10", SqlDbType.NVarChar, 20).Value = TextBox26.Text
                .Parameters.Add("@CAP11", SqlDbType.NVarChar, 20).Value = TextBox27.Text
                .Parameters.Add("@CAP12", SqlDbType.NVarChar, 20).Value = TextBox28.Text
                .Parameters.Add("@CAP13", SqlDbType.NVarChar, 20).Value = TextBox29.Text
                .Parameters.Add("@CAP14", SqlDbType.NVarChar, 20).Value = TextBox30.Text
                .Parameters.Add("@MOD1", SqlDbType.NVarChar, 20).Value = ComboBox1.Text
                .Parameters.Add("@MOD2", SqlDbType.NVarChar, 20).Value = ComboBox2.Text
                .Parameters.Add("@MOD3", SqlDbType.NVarChar, 20).Value = TextBox30.Text
                .Parameters.Add("@CON1", SqlDbType.NVarChar, 20).Value = TextBox31.Text
                .Parameters.Add("@CON2", SqlDbType.NVarChar, 20).Value = TextBox32.Text
                .Parameters.Add("@CON3", SqlDbType.NVarChar, 20).Value = TextBox33.Text
                .Parameters.Add("@CON4", SqlDbType.NVarChar, 20).Value = TextBox34.Text
                .Parameters.Add("@COLL1", SqlDbType.NVarChar, 20).Value = CheckBox1
                .Parameters.Add("@COLL2", SqlDbType.NVarChar, 20).Value = CheckBox2
                .Parameters.Add("@COLL3", SqlDbType.NVarChar, 20).Value = CheckBox3
                .Parameters.Add("@COLL4", SqlDbType.NVarChar, 20).Value = CheckBox4
                .Parameters.Add("@COLL5", SqlDbType.NVarChar, 20).Value = CheckBox5
                .Parameters.Add("@COLL1", SqlDbType.NVarChar, 20).Value = CheckBox6
                .Parameters.Add("@COLL2", SqlDbType.NVarChar, 20).Value = CheckBox7
                .Parameters.Add("@COLL3", SqlDbType.NVarChar, 20).Value = CheckBox8
                .Parameters.Add("@COLL4", SqlDbType.NVarChar, 20).Value = CheckBox9
                .Parameters.Add("@COLL5", SqlDbType.NVarChar, 20).Value = CheckBox10
                .Parameters.Add("@HASIL1", SqlDbType.NVarChar, 20).Value = CheckBox13
                .Parameters.Add("@HASIL2", SqlDbType.NVarChar, 20).Value = CheckBox14
                .Parameters.Add("@HASIL3", SqlDbType.NVarChar, 20).Value = CheckBox15
                .Parameters.Add("@HASIL4", SqlDbType.NVarChar, 20).Value = CheckBox16
                .Parameters.Add("@HASIL1", SqlDbType.NVarChar, 20).Value = CheckBox17
                .Parameters.Add("@HASIL2", SqlDbType.NVarChar, 20).Value = CheckBox18
                .Parameters.Add("@HASIL3", SqlDbType.NVarChar, 20).Value = CheckBox19
                .Parameters.Add("@HASIL4", SqlDbType.NVarChar, 20).Value = CheckBox20

                .ExecuteNonQuery()
            End With
            simpan.Commit()
            MsgBox("Data Berhasil di Simpan")
            bersih()
        Catch ex As Exception
            MsgBox("Gagal tersimpan", MsgBoxStyle.Critical, "PERINGATAN")
        End Try
    End Sub

MY MODULE

Imports System.Data.Sql
Imports System.Data.SqlClient

Module koneksi
    Public kon As SqlConnection
    Public cmd As SqlCommand
    Public da As SqlDataAdapter
    Public dr As SqlDataReader
    Public dt As DataTable
    Public data As DataSet
    Public myRow As DataRow

    Public Sub get_kon()
        Dim server As String
        server = "Data Source=**************;Initial Catalog=CobaDbInput;User ID=sa;Password=***********"
        kon = New SqlConnection(server)
        Try
            If kon.State = ConnectionState.Closed Then
                kon.Open()
            End If
        Catch ex As Exception
            MsgBox(Err.Description, MsgBoxStyle.Critical, "ERROR")
        End Try
    End Sub
End Module

Hi

What is the actual problem? Are you getting any errors?

Some notes on your code, you should really get into the habit of naming your controls so that they mean something when you are reading the code. Looking at your code at the moment, all I see is TextBox1, TextBox2 etc. This makes it very difficult to follow and will cause you issues down the road, especially when you are mapping the contents to parameters.

I also see that you are assign CheckBox1, CheckBox2 etc. to parameters, but you are not stating what property of the CheckBox you want to use. Is it the check boxes state (checked, not checked) or something else?

In your get_con routine you are checking if the state of the connection is closed, this is not required as you are instantiating the connection above so by default it will be in a closed state.

Hi too
when I execute a stored proc, the data failed to enter into the database. so this is wrong the source code on the save button

Hi

I have tested your code and it does work in my simple test. Are you getting your error message box "Gagal tersimpan"? If so, I would recommend commenting this out for now and replacing with MessageBox.Show(ex.Message) so that you can see what the exception is.

means I have to change the checkbox to a string type?

means I have to change the checkbox to a string type?

That depends on what you want to get from the check box. If you change it to a string type using something like: CheckBox1.ToString this will simply return "System.Windows.Forms.CheckBox, CheckState: 0" which is probably not what you want. If you want to get the state of the check box (checked or not) then use CheckBox1.Checked.ToString which will return "True" or "False".

I want it contains data that checkbox is checked in the checkbox. but when I tried the execution of key store proc notification "procedure or function windows form has too many arguments specified vbnet"
sorry previously troublesome

I want it contains data that checkbox is checked in the checkbox

For that, use CheckBox1.Checked.ToString assuming that this is going to a NVarChar field.

but when I tried the execution of key store proc notification "procedure or function windows form has too many arguments specified vbnet"

How is your stored procedure created, are you passing too many parameters?

ye,i use too many param in my stored proc.
does it really affect?

ye,i use too many param in my stored proc.
does it really affect?

Yes, it will affect the running of the query. If your stored procedure requires 2 parameters but you specify 3 then it will not work.

If i change my param like this

.Parameters.Add("@NONAS", SqlDbType.NVarChar, 12).Value = TextBox1.Text
                .Parameters.Add("@NAMA", SqlDbType.NVarChar, 75).Value = TextBox2.Text
                .Parameters.Add("@ALAMAT", SqlDbType.NVarChar, 75).Value = TextBox3.Text
                .Parameters.Add("@NOKTP", SqlDbType.NVarChar, 25).Value = TextBox4.Text
                .Parameters.Add("@CHAR1", SqlDbType.NVarChar, 20).Value = TextBox5.Text
                .Parameters.Add("@CHAR2", SqlDbType.NVarChar, 20).Value = TextBox6.Text
                .Parameters.Add("@CHAR3", SqlDbType.NVarChar, 20).Value = TextBox7.Text
                .Parameters.Add("@CHAR4", SqlDbType.NVarChar, 20).Value = TextBox8.Text
                .Parameters.Add("@CHAR5", SqlDbType.NVarChar, 20).Value = TextBox9.Text
                .Parameters.Add("@CHAR6", SqlDbType.NVarChar, 20).Value = TextBox10.Text
                .Parameters.Add("@CHAR7", SqlDbType.NVarChar, 20).Value = TextBox11.Text
                .Parameters.Add("@CHAR8", SqlDbType.NVarChar, 20).Value = TextBox12.Text
                .Parameters.Add("@CHAR9", SqlDbType.NVarChar, 20).Value = TextBox13.Text
                .Parameters.Add("@CHAR10", SqlDbType.NVarChar, 20).Value = TextBox14.Text
                .Parameters.Add("@CHAR11", SqlDbType.NVarChar, 20).Value = TextBox15.Text
                .Parameters.Add("@CHAR12", SqlDbType.NVarChar, 20).Value = TextBox16.Text
                .Parameters.Add("@CAP1", SqlDbType.NVarChar, 20).Value = TextBox17.Text
                .Parameters.Add("@CAP2", SqlDbType.NVarChar, 20).Value = TextBox18.Text
                .Parameters.Add("@CAP3", SqlDbType.NVarChar, 20).Value = TextBox19.Text
                .Parameters.Add("@CAP4", SqlDbType.NVarChar, 20).Value = TextBox20.Text
                .Parameters.Add("@CAP5", SqlDbType.NVarChar, 20).Value = TextBox21.Text
                .Parameters.Add("@CAP6", SqlDbType.NVarChar, 20).Value = TextBox22.Text
                .Parameters.Add("@CAP7", SqlDbType.NVarChar, 20).Value = TextBox23.Text
                .Parameters.Add("@CAP8", SqlDbType.NVarChar, 20).Value = TextBox24.Text
                .Parameters.Add("@CAP9", SqlDbType.NVarChar, 20).Value = TextBox25.Text
                .Parameters.Add("@CAP10", SqlDbType.NVarChar, 20).Value = TextBox26.Text
                .Parameters.Add("@CAP11", SqlDbType.NVarChar, 20).Value = TextBox27.Text
                .Parameters.Add("@CAP12", SqlDbType.NVarChar, 20).Value = TextBox28.Text
                .Parameters.Add("@CAP13", SqlDbType.NVarChar, 20).Value = TextBox29.Text
                .Parameters.Add("@CAP14", SqlDbType.NVarChar, 20).Value = TextBox30.Text
                .Parameters.Add("@MOD1", SqlDbType.NVarChar, 20).Value = CheckBox1.ToString = "Ya"
                .Parameters.Add("@MOD1", SqlDbType.NVarChar, 20).Value = CheckBox1.ToString = "Tidak"
                .Parameters.Add("@MOD2", SqlDbType.NVarChar, 20).Value = CheckBox1.ToString = "Ya"
                .Parameters.Add("@MOD2", SqlDbType.NVarChar, 20).Value = CheckBox1.ToString = "Tidak"
                .Parameters.Add("@MOD3", SqlDbType.NVarChar, 20).Value = TextBox30.Text
                .Parameters.Add("@CON1", SqlDbType.NVarChar, 20).Value = TextBox31.Text
                .Parameters.Add("@CON2", SqlDbType.NVarChar, 20).Value = TextBox32.Text
                .Parameters.Add("@CON3", SqlDbType.NVarChar, 20).Value = TextBox33.Text
                .Parameters.Add("@CON4", SqlDbType.NVarChar, 20).Value = TextBox34.Text
                .Parameters.Add("@COLL1", SqlDbType.NVarChar, 20).Value = CheckBox7.ToString = "Ya"
                .Parameters.Add("@COLL1", SqlDbType.NVarChar, 20).Value = CheckBox12.ToString = "Tidak"
                .Parameters.Add("@COLL2", SqlDbType.NVarChar, 20).Value = CheckBox8.ToString = "Ya"
                .Parameters.Add("@COLL2", SqlDbType.NVarChar, 20).Value = CheckBox13.ToString = "Tidak"
                .Parameters.Add("@COLL3", SqlDbType.NVarChar, 20).Value = CheckBox8.ToString = "Ya"
                .Parameters.Add("@COLL3", SqlDbType.NVarChar, 20).Value = CheckBox14.ToString = "Tidak"
                .Parameters.Add("@COLL4", SqlDbType.NVarChar, 20).Value = CheckBox10.ToString = "Ya"
                .Parameters.Add("@COLL4", SqlDbType.NVarChar, 20).Value = CheckBox15.ToString = "Tidak"
                .Parameters.Add("@COLL5", SqlDbType.NVarChar, 20).Value = CheckBox11.ToString = "Ya"
                .Parameters.Add("@COLL5", SqlDbType.NVarChar, 20).Value = CheckBox16.ToString = "Tidak"
                .Parameters.Add("@HASIL1", SqlDbType.NVarChar, 20).Value = CheckBox17.ToString = "Ya"
                .Parameters.Add("@HASIL1", SqlDbType.NVarChar, 20).Value = CheckBox21.ToString = "Tidak"
                .Parameters.Add("@HASIL2", SqlDbType.NVarChar, 20).Value = CheckBox18.ToString = "Ya"
                .Parameters.Add("@HASIL2", SqlDbType.NVarChar, 20).Value = CheckBox23.ToString = "Tidak"
                .Parameters.Add("@HASIL3", SqlDbType.NVarChar, 20).Value = CheckBox19.ToString = "Ya"
                .Parameters.Add("@HASIL3", SqlDbType.NVarChar, 20).Value = CheckBox24.ToString = "Tidak"
                .Parameters.Add("@HASIL4", SqlDbType.NVarChar, 20).Value = CheckBox20.ToString = "Ya"
                .Parameters.Add("@HASIL4", SqlDbType.NVarChar, 20).Value = CheckBox24.ToString = "Tidak"

whether these parameters could be run?

Does your stored procedure contain that many parameters? In fact, can you post your stored procedure code.

This my stored procedure code

CREATE PROC insertdata
@NONAS nvarchar(12), 
@NAMA nvarchar(75), 
@ALAMAT nvarchar(75), 
@NOKTP nvarchar(25), 
@CHAR1 nvarchar(20), 
@CHAR2 nvarchar(20), 
@CHAR3 nvarchar(20), 
@CHAR4 nvarchar(20), 
@CHAR5 nvarchar(20), 
@CHAR6 nvarchar(20), 
@CHAR7 nvarchar(20), 
@CHAR8 nvarchar(20), 
@CHAR9 nvarchar(20), 
@CHAR10 nvarchar(20), 
@CHAR11 nvarchar(20), 
@CHAR12 nvarchar(20), 
@CAP1 nvarchar(20), 
@CAP2 nvarchar(20), 
@CAP3 nvarchar(20), 
@CAP4 nvarchar(20), 
@CAP5 nvarchar(20), 
@CAP6 nvarchar(20),
@CAP7 nvarchar(20),
@CAP8 nvarchar(20),
@CAP9 nvarchar(20),
@CAP10 nvarchar(20), 
@CAP11 nvarchar(20), 
@CAP12 nvarchar(20), 
@CAP13 nvarchar(20), 
@CAP14 nvarchar(20), 
@MOD1 nvarchar(20), 
@MOD2 nvarchar(20), 
@MOD3 nvarchar(20), 
@CON1 nvarchar(20), 
@CON2 nvarchar(20), 
@CON3 nvarchar(20), 
@CON4 nvarchar(20), 
@COLL1 nvarchar(20), 
@COLL2 nvarchar(20), 
@COLL3 nvarchar(20), 
@COLL4 nvarchar(20), 
@COLL5 nvarchar(20), 
@HASIL1 nvarchar(20), 
@HASIL2 nvarchar(20), 
@HASIL3 nvarchar(20), 
@HASIL4 nvarchar(20)
AS
INSERT INTO tb5C
                      (NONAS, NAMA, ALAMAT, NOKTP, CHAR1, CHAR2, CHAR3, CHAR4, CHAR5, CHAR6, CHAR7, CHAR8, CHAR9, CHAR10, CHAR11, CHAR12, CAP1, CAP2, CAP3, CAP4, CAP5, CAP6, CAP7, CAP8, CAP9, CAP10, CAP11, CAP12, CAP13, CAP14, MOD1, MOD2, MOD3, CON1, CON2, CON3, CON4, COLL1, COLL2, COLL3, COLL4, COLL5, HASIL1, HASIL2, HASIL3, HASIL4)

VALUES     (@NONAS,@NAMA,@ALAMAT,@NOKTP,@CHAR1,@CHAR2,@CHAR3,@CHAR4,@CHAR5,@CHAR6,@CHAR7,@CHAR8,@CHAR9,@CHAR10,@CHAR11,@CHAR12,@CAP1,@CAP2,@CAP3,@CAP4,@CAP5,@CAP6,@CAP7,@CAP8,@CAP9,@CAP10,@CAP11,@CAP12,@CAP13,@CAP14,@MOD1,@MOD2,@MOD3,@CON1,@CON2,@CON3,@CON4,@COLL1,@COLL2,@COLL3,@COLL4,@COLL5,@HASIL1,@HASIL2,@HASIL3,@HASIL4)

Ok, I see the problem now. You are duplicating parameters for your check boxes. For example:

.Parameters.Add("@MOD1", SqlDbType.NVarChar, 20).Value = "Ya"
.Parameters.Add("@MOD1", SqlDbType.NVarChar, 20).Value = "Tidak"

Why are you doing this? This is repeated for all of your Check Boxes.

By only supplying one parameter per check box value your code works fine. So what is your intention? What do you want to store in MOD1 for example?

My intention is, if checkbox.checked = true, then the existing data stored in the database.

but if i add one param in my checkbox, in vb.net displays an error message "procedure or function has too many arguments specified"

So, what should I do?

My intention is, if checkbox.checked = true, then the existing data stored in the database.

Well you could store Null if the Check box is not checked:

If checkbox.checked = true Then
    .Parameters.Add("@MOD1", SqlDbType.NVarChar, 20).Value = "Ya"
Else
    .Parameters.Add("@MOD1", SqlDbType.NVarChar, 20).Value = DBNull.Value
End If

But if you want to store True or False then simply use one parameter:

.Parameters.Add("@MOD1", SqlDbType.NVarChar, 20).Value = checkbox.checked.ToString

Or if you want to use "Ya" and "Tidak" then:

If checkbox.checked = true Then
    .Parameters.Add("@MOD1", SqlDbType.NVarChar, 20).Value = "Ya"
Else
    .Parameters.Add("@MOD1", SqlDbType.NVarChar, 20).Value = "Tidak"
End If

Edited 1 Year Ago by djjeavons

but in this case I use a lot of checkboxes, then in one param I use two or more checkboxes.
so I must use the source code which one?

ex
in one param i have a three checkboxes and one textbox, so when the checkbox 1 or 2.checked then text in the checkboxes will stored into the database.

but when checkbox3.checked = true then textbox1.enable = true and data in the textbox will stored into database

so how the source code?

but later on I will reply your message within the next few hours, Because I'm in affairs.

Thanks for your help today, see you next few hours

At the end of the day, you can only specify the parameter for the stored procedure once. So in your example where if checkbox1 or checkbox2 is checked then you store some data in the database you will have to code the logic for that:

If CheckBox1.Checked = True OrElse CheckBox2.Checked = True Then
    .Parameters.Add("@WhateverParameter", SqlDbType.NVarChar, 20).Value = "your Value"
Else
    .Parameters.Add("@WhateverParameter", SqlDbType.NVarChar, 20).Value = DBNull.Value
End If

but checkbox 1 and 2 not same, whether these parameters can be combined into one or split but within a parameter?

but checkbox 1 and 2 not same, whether these parameters can be combined into one or split but within a parameter?

Can you give an example of what you would expect this data to look like in the database table based on different scenario's?

ok, I finished with my code.
but when it is executed out the following notification

this my prob
Click Here

Edited 1 Year Ago by vdls

If you are getting a timeout error then the first thing to do would be to set the CommandTimeout property of the Command object to something longer to ensure that the statement can indeed execute.

If the above works, I would then suggest looking into why the statement is taking so long (as the default Timeout is 30 seconds), maybe you need to optimise your database or look at reducing the complexity of your statement.

keeps the solution to resolve the issue of how?
if I have to change my db query, or add a command timeout in my vb.net

Did you try changing the Command Timeout property? I suggest doing this first to make sure that your query does execute (regardless of how long it takes). This then at least tells you that it is correct (even if it is slow). If it does work, then you will look into why it is slow and perform optimisation techniques (which is beyond the scope of this thread) to speed it up.

basically I want to insert data from a checkbox or textbox into a database using stored proceure

Yes, I understand what you want to do. But your last error was that the query was timing out so I am assuming at this point that you are still not yet writing data to the database because of this.

What I am saying is that you should increase the timeout command.CommandTimeout = 60 and try to run the query again. If it still times out, increase the time out value until it executes or gives you a different error. Once you have determined if the stored procedure works, you can then look into why it is taking so long that it causes a Timeout exception.

This means that you are inserting a duplicate value into a column of your table that is defined as a primary key. Primary keys cannot contain duplicate values.

whether there is a manufacturing fault or stored proc me there is a problem with my coding?
every time i try to input the data by running my project in vb.net data can not be entered.
whereas when I exec sql server it's works.
so this problem is in part what?
you have ym or more for live chat?

Edited 1 Year Ago by vdls

whereas when I exec sql server it's works.

If this is the case, then while I don't think there is a problem with your code, there is certainly a problem with the values that you are supplying.

I would recommend that you run SQL Server Profiler and do the following:

  1. Run the stored procedure via SQL Server and capture the TextData output from the profiler.
  2. Run the stored procedure from your code and capture the TextData output from the profiler.
  3. Compare the two pieces of data to see if indeed you are providing duplicate data.

Post those results back.

If you haven't used the profiler before, then start it up and do the following:

  1. File > New Trace
  2. Connect to your instance of SQL Server
  3. Click the Events Selection tab
  4. Uncheck everything in the Events column, BUT leave the Stored Procedures > RPC Completed checked
  5. Check the TextData next to RPC Completed
  6. Click Run
  7. Now execute the stored procedure from SQL Server and capture the text data
  8. Execute the stored procedure from your code and capture the text data
  9. Stop the profiler.
This article has been dead for over six months. Start a new discussion instead.