[RESOLVED] Save boolean value to MS Database

[Tengkorak]

hi,
i have problem when save boolean value to database.
this my code :

if bAdd then 
     scmd.CommandText = "insert into products(prod_id,status) values(@prod_id,@status)"
else
     scmd.CommandText = "update products set status=@status where prod_id=@prod_id"
end if
 
scmd.Parameters.Add(New OleDbParameter("@status", CBool(chkStatus.Checked)))
 
scmd.Connection = dbConn
Dim iResult As Integer = scmd.ExecuteNonQuery

i get problem when execute query at line

scmd.ExecuteNonQuery

Data type mismatch in criteria expression

[jmcilhinney]

First up, there's no point using CBool in that code because the Checked property of a CheckBox is already type Boolean.

As for the question, it relates to how your parameters are being handled. Even though Access/Jet/OleDb allows you to use names for your parameters, that is only for your benefit. The names are not actually used internally. It's only the position of the parameters that matter. You have a problem because in your INSERT statement @prod_id comes BEFORE @status and in the UPDATE statement it comes AFTER. That means that, for one or the other, the parameters you add will be the wrong way around.

Whatever order the parameters appear in the SQL code is the order you must add them to the command. You need to either put the parameters in the same order in both SQL statements or else add the parameters in a different order depending on the SQL statement.

[Tengkorak]

thank for your reply
but I still haven't been able to store the boolean data.
I've change my code but still haven't been able too.

just add the information.
I use the microsoft access database, the data type of the field status is YES/NO

[jmcilhinney]

How can we tell you what's wrong with the new code if you don't show it to us?

[Tengkorak]

this my complate code :

If bAdd Then
                scmd.CommandText = "insert into inv_t_products (prod_id,prod_name,unit,unitprice,id_group_prod,supp_id,konsinyasi) " & _
                        "values(@prodid,@prodname,@unit,@unitprice,@idgroup,@idsupp,@konsinyasi)"
 
                scmd.Parameters.Add(New OleDbParameter("@prodid", txtKd.Text))
 
 
                'Debug.Print("Insert Mode")
            Else
                'modify
                scmd.CommandText = "update inv_t_products set prod_name=@prodname,unit=@unit,unitprice=@unitprice, " & _
                    "id_group_prod=@idgroup,supp_id=@idsupp ,konsinyasi=@konsinyasi " & _
                    "where prod_id='" & txtKd.Text & "'"
 
 
            End If
 
 
            scmd.Parameters.Add(New OleDbParameter("@prodname", txtNm.Text))
            scmd.Parameters.Add(New OleDbParameter("@unit", txtSat.Text))
            scmd.Parameters.Add(New OleDbParameter("@unitprice", txtHarga.Text))
            scmd.Parameters.Add(New OleDbParameter("@idgroup", t_kategori))
            scmd.Parameters.Add(New OleDbParameter("@idsupp", t_supplier))
            'scmd.Parameters.Add(New OleDbParameter("@konsinyasi", chkKonsy.Checked))
 
            Dim stsPar As New OleDbParameter
            stsPar.OleDbType = OleDbType.Boolean
            stsPar.ParameterName = "konsinyasi"
            stsPar.Value = chkKonsy.Checked
 
            scmd.Parameters.Add(stsPar)
 
           
            scmd.Connection = dbConn
            Dim iResult As Integer = scmd.ExecuteNonQuery

but data not saved and get same error.

[Ram2Curious]

Instead of using this

scmd.Connection = dbConn
      Dim iResult As Integer = scmd.ExecuteNonQuery

Try this

scmd.Connection = dbConn
       scmd.ExecuteNonQuery()

[Tengkorak]

Dim iResult As Integer = scmd.ExecuteNonQuery

if value of iResult = 1 means execute success and 0 is not.

my problem at field konsinyasi

stsPar.ParameterName = "konsinyasi"

i can not save to YES/NO data type at ms access database.

[jmcilhinney]

You have ignored what I said in my previous post. I said that you must add the parameters to the command in the same order that they appear in the SQL code. You are adding them in a different order. Obviously, if you don;t fix the problem then the problem won't be fixed.

Also, you should be calling AddWithValue rather than Add.

Follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data. One of the examples there uses ExecuteNonQuery. Follow that example.

[Tengkorak]

thank you @jmcilhinney
already successful now