TheWizardofInt Posted February 22, 2005 Posted February 22, 2005 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] Quote Read the Fovean Chronicles Because you just can't spend your whole day programming!
stustarz Posted February 23, 2005 Posted February 23, 2005 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 Quote Visit: VBSourceSeek - The VB.NET sourcecode library "A mere friend will agree with you, but a real friend will argue."
Administrators PlausiblyDamp Posted February 23, 2005 Administrators Posted February 23, 2005 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)... Quote Posting Guidelines FAQ Post Formatting Intellectuals solve problems; geniuses prevent them. -- Albert Einstein
TheWizardofInt Posted February 23, 2005 Author Posted February 23, 2005 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 Quote Read the Fovean Chronicles Because you just can't spend your whole day programming!
Administrators PlausiblyDamp Posted February 23, 2005 Administrators Posted February 23, 2005 I think you just use a ? no name etc sSQL = "UPDATE [sales] SET [Client]=?, [sDate]=?, [Gross]=?," & _ " [Net]=?, [Product]=?, [Developer]=?, [Hours]=?, [Paid]=?" Quote Posting Guidelines FAQ Post Formatting Intellectuals solve problems; geniuses prevent them. -- Albert Einstein
TheWizardofInt Posted February 23, 2005 Author Posted February 23, 2005 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? Quote Read the Fovean Chronicles Because you just can't spend your whole day programming!
HJB417 Posted February 23, 2005 Posted February 23, 2005 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. Quote
*Gurus* Derek Stone Posted February 24, 2005 *Gurus* Posted February 24, 2005 So then it just takes the parameters in order of how you created them? Yep. Stupid ain't it? Quote Posting Guidelines
TheWizardofInt Posted March 1, 2005 Author Posted March 1, 2005 Wow - did that ever work Thanks for all of the help Quote Read the Fovean Chronicles Because you just can't spend your whole day programming!
cpopham Posted March 2, 2005 Posted March 2, 2005 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 Quote ____________________________________________ http://www.pophamcafe.com I am starting a developers section, more tutorials than anything.
HJB417 Posted March 2, 2005 Posted March 2, 2005 If by sql, you mean msssql, then I believe mssql supports unnamed parameters. Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.