Datatype MisMatch

TheWizardofInt

TheWizardofInt

Well-known member
Joined
Dec 31, 1969
Messages
333
Location
Orlando, FL
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
Code: 
 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
 
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
 
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 doesnt 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)...
 
PlausiblyDamp said:
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 doesnt 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.
Click to expand...

Everyone calls me Wizard (I just dont feel like one when I come here)

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

And thanks for the help you always give me, and that I always appreciate
 
I think you just use a ? no name etc
Code: 
sSQL = "UPDATE [Sales] SET [Client]=?, [SDate]=?, [Gross]=?," & _
            " [Net]=?, [Product]=?, [Developer]=?, [Hours]=?, [Paid]=?"
 
PlausiblyDamp said:
I think you just use a ? no name etc
Code: 
sSQL = "UPDATE [Sales] SET [Client]=?, [SDate]=?, [Gross]=?," & _
            " [Net]=?, [Product]=?, [Developer]=?, [Hours]=?, [Paid]=?"
Click to expand...

So then it just takes the parameters in order of how you created them?
 
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.:

[VB]
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] + ".");
}
[/VB]

note though, the column data type in access is a datetime, not a string.
 
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
 
Back
Top