Datatype MisMatch

[TheWizardofInt]

Anyone see a problem with this? I am writing to a simple Access database

 

Client = String (all strings are 50)

sDate = Date/Time

Gross= Currency

Net=Currency

Product = String

Developer = String

Hours = Long Integer

Paid = Boolean

 sSQL = "UPDATE [sales] SET [Client]=@Client, [sDate]=@SDate, [Gross]=@Gross," & _

            " [Net]=@Net, [Product]=@Product, [Developer]=@Developer, [Hours]=@Hours, [Paid]=@Paid"



        Try

            oConn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " & _

                "Data Source=" & sPath & ";")

            oConn.Open()

            cmd = New OleDb.OleDbCommand



            cmd.Parameters.Add(New OleDb.OleDbParameter("@SDate", System.Data.OleDb.OleDbType.DBDate, 8))

            cmd.Parameters("@SDate").Value = CDate(txtDate.Text)

            cmd.Parameters.Add(New OleDb.OleDbParameter("@Client", System.Data.OleDb.OleDbType.Char, 50))

            cmd.Parameters("@Client").Value = txtClient.Text

            cmd.Parameters.Add(New OleDb.OleDbParameter("@Gross", System.Data.OleDb.OleDbType.Currency, 8))

            cmd.Parameters("@Gross").Value = Val(txtCost.Text)

            cmd.Parameters.Add(New OleDb.OleDbParameter("@Net", System.Data.OleDb.OleDbType.Currency, 8))

            cmd.Parameters("@Net").Value = Val(txtRate.Text)

            cmd.Parameters.Add(New OleDb.OleDbParameter("@Product", System.Data.OleDb.OleDbType.Char, 50))

            cmd.Parameters("@Product").Value = txtProduct.Text

            cmd.Parameters.Add(New OleDb.OleDbParameter("@Developer", System.Data.OleDb.OleDbType.Char, 50))

            cmd.Parameters("@Developer").Value = cmbDeveloper.Text

            cmd.Parameters.Add(New OleDb.OleDbParameter("@Hours", System.Data.OleDb.OleDbType.Numeric, 8))

            cmd.Parameters("@Hours").Value = Val(txtHours.Text)

            cmd.Parameters.Add(New OleDb.OleDbParameter("@Paid", System.Data.OleDb.OleDbType.Boolean, 1))

            cmd.Parameters("@Paid").Value = b



            cmd.Connection = oConn

            cmd.CommandText = sSQL

[/Code]

[stustarz]

Ive never really used this method as it seems easier to use something like:

 

sSQL = "UPDATE [sales] SET [Client]=' " & txtClient.Text & " ' etc...

 

But, i would change all the datatypes to .Variant as this will allow the command to map to the correct datatype, or even easier - just dont specify the datatype - it defaults to Datatype.Variant anyway if one isnt specified

[PlausiblyDamp]

Wizard (may I call you that?) - does it give any further information as to which line / parameter is generating the error? From a quick glance it looks fine.

Edit: something just came to mind: IIRC OleDb doesn't support named parameters - i think you need to just use a ? character in place of the parameter and make sure you add them in in the correct order; which could explain the mismatch as you are adding them in a different order to the sSQL string.

 

stustarz - the problem with that approach is that it can lead to problems with strings needing to be converted by the DB (dates are a prime example where this can be fun), opens your system to potential security exploits (search for SQL Injection) and can make the code more difficult to maintain as the size of the SQL string increases / number of parameters increase as well as the joy of handling textboxes that contain double or single quotes or both types of quotes (work on a BOM system where measurements are given as 10' 6" and see what happens)...

[TheWizardofInt]

Wizard (may I call you that?) - does it give any further information as to which line / parameter is generating the error? From a quick glance it looks fine.

Edit: something just came to mind: IIRC OleDb doesn't support named parameters - i think you need to just use a ? character in place of the parameter and make sure you add them in in the correct order; which could explain the mismatch as you are adding them in a different order to the sSQL string.

 

 

Everyone calls me Wizard (I just don't feel like one when I come here)

 

Are you saying to replace the @ with a '?'? It doesn't give any line parameters errors, just the mismatch

 

And thanks for the help you always give me, and that I always appreciate

[PlausiblyDamp]

I think you just use a ? no name etc

sSQL = "UPDATE [sales] SET [Client]=?, [sDate]=?, [Gross]=?," & _
           " [Net]=?, [Product]=?, [Developer]=?, [Hours]=?, [Paid]=?"

[TheWizardofInt]

I think you just use a ? no name etc

sSQL = "UPDATE [sales] SET [Client]=?, [sDate]=?, [Gross]=?," & _
           " [Net]=?, [Product]=?, [Developer]=?, [Hours]=?, [Paid]=?"

 

So then it just takes the parameters in order of how you created them?

[HJB417]

I remember I had problems storing a System.DateTime value as a DateTime in access. I was storing it as a System.Data.DbTypes.DateTime, I instead had to store it in the parameter as a string instead of a DateTime, e.x.:

 

public static void Add(CreditCardInfo ccInfo, Response response)
{
GenericDbCommandHelper cmd = Settings.DatabaseConnection.CreateCommand();
cmd.CommandText = "INSERT INTO transactions(account, transactionid, amount, dateadded) VALUES(@account, @transactionid, @amount, @dateadded)";
cmd.Parameters.Add("@account", CreditCardCrypter.Encrypt(ccInfo));
cmd.Parameters.Add("@transactionid", response[RequestFields.TransactionID]);
cmd.Parameters.Add("@amount", response[RequestFields.Amount]);
cmd.Parameters.Add("@dateadded", DateTime.Now.ToString());
Trace.WriteLine("Adding transaction id:" + response[RequestFields.TransactionID] + " with $" + response[RequestFields.Amount] + ".");
cmd.ExecuteNonQuery();
Trace.WriteLine("Added transaction id:" + response[RequestFields.TransactionID] + " with $" + response[RequestFields.Amount] + ".");
}

 

note though, the column data type in access is a datetime, not a string.

[Derek Stone]

So then it just takes the parameters in order of how you created them?

Yep. Stupid ain't it?

[TheWizardofInt]

Wow - did that ever work

 

Thanks for all of the help

[HJB417]

Join Date: Jan 1970?

=D

[cpopham]

Parameters are still more efficient to use than placing the variable straight into your SQL statement. Parameters are also easier to adapt across platforms. If you make the switch from Access to SQL, then you would only have to replace the "?" in the SQL statements with the parameters names.

 

Chester

[HJB417]

If by sql, you mean msssql, then I believe mssql supports unnamed parameters.