trouble with parameterized queries

[bchaney]

Hi all. i'm trying to learn how to write paramterized queries. from what i've pieced together on the internet i've made a few efforts. i'm having trouble though and i'm sure it's something i'm not doing right.

here is my insert code and parameters i'm trying to enter. i have ms access 2003:

insstr = "INSERT INTO downtimetable ( [Mechanic Name], Shift, [Line Number], [Machine Description], [Failure Point], [Cause of Failure], [Estimated Time], [Equipment Number], [WO Status] )" & _
"VALUES (@stoName, @stoDate, @stoShift, @stoLine, @stoMachine, @stoFailure, @stoCause, @stoEstTime, @stoEquipment_Number, @stoWOStatus);"
msgbox insstr, vbOKOnly, "test"
With cmd
    Set .ActiveConnection = conn
    .CommandText = insstr
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("stoName", adVarChar, adParamInput, Len(cboUsername.value), cboUsername.value)
    .Parameters.Append .CreateParameter("stoDate", adDate, adParamInput, Len(txtDate), txtDate)
    .Parameters.Append .CreateParameter("stoShift", adInteger, adParamInput, Len(txtShift), txtShift)
    .Parameters.Append .CreateParameter("stoLine", adVarChar, adParamInput, Len(cboLine.value), cboLine.value)
    .Parameters.Append .CreateParameter("stoMachine", adVarChar, adParamInput, Len(cboMachine.value), cboMachine.value)
    .Parameters.Append .CreateParameter("stoFailure", adVarChar, adParamInput, Len(cboFailure.value), cboFailure.value)
    .Parameters.Append .CreateParameter("stoCause", adVarChar, adParamInput, Len(txtFailure), txtFailure)
    .Parameters.Append .CreateParameter("stoEstTime", adInteger, adParamInput, Len(txtEstTime), txtEstTime)
    .Parameters.Append .CreateParameter("stoEquipment_Number", adInteger, adParamInput, Len(txtEquipment_Number), txtEquipment_Number)
    .Parameters.Append .CreateParameter("stoWOStatus", adVarChar, adParamInput, Len(chkstatus), chkstatus)
End With

after i try and run this the application errors out with a type mismatch. i have tried changing numerous fields around tring different "ad-" types and all come with the same result. my database column id's are what is shown above. i put a msgbox catch in the code right before and right after the with statement. i see the msgbox before but not after. so i'm getting thrown out at the with statement. the code is fine and the insert string looks good up to that point.

all of this code is together in the onclick event of a save button.

What am i doing wrong?

Thanks

 

[pbaldy]

Let's clarify; do you have a stored procedure ( on SQL Server or something) that does the insert and you're trying to pass parameters to it, or is this code intended to do the actual insert? You're sort of mixing techniques here.

 

[bchaney]

it's kinda both. i saw code that showed how to make a stored procedure. however this sql statement is only going to be used for this particular insert. but the insert has a field where the user can input all different kinds of characters: ',",/, etc. so looking online everyone said the only way to go is with parameterized queries. well i couldn't find anything that explains exactly how to do it so i kind of put procedures from different sources together and tried to get it to work. what's above is my best effort. i usually use dynamic sql for my statements and limit the input fields to certain characters. however, one of my fields is a description type box that allows the user to input various things. any insight into how to properly set up parameterized queries would be appreciated.

Thanks

 

[bchaney]

sorry i didn't answer your question with my response above. this code was originally doing the insert statement. but i ran into problems with the characters ' and ". those characters have to be allowed in the description fields and from what i've gathered reading online, the only way to do that is to make the sql string a parameterized query. i thought stored procedures and parameterized queries were the same thing.

i'm running access 2003 front end with a mysql database backend. the code with just a dynamic sql works fine but can't take the characters mentioned above.

 

[bchaney]

Okay sorry to bother you guys about this post. I kind of figured out the problem for the code above and kind of stumbled on a new problem. The above problem was due to me trying to msgbox cmd. which i found out i cannot do. That was giving me the mismatch error. I commented that part out for debugging and now after the "End With" the code is erroring out. The next line is:

myrs.Open cmd, , adOpenKeyset, adLockOptimistic

The error that comes up is ODBC driver does not support the requested properties. So I made the code as such:

myrs.open cmd

This code now brings up a syntax error from MySQL. I check the syntax of the INSERT INTO string and it looks right. Does the columns have to match the database columns in terms of order? This is where I'm now stuck.

Can someone point me in the right direction? Am I going about this the wrong way?

Thanks

 

[pbaldy]

I haven't used MySQL but I use SQL Server most of the time. In that world, they are different animals. A stored procedure lives in SQL Server, and has the actual SQL. Your ADO command code would simply execute the SP after passing the parameters to it (your .Parameters.Append lines). A friend wrote a FAQ that deals with the issue of having single and double quotes within the text. See if it helps:

http://www.baldyweb.com/BuildSQL.htm

If not, post your full code now so we can see what might be going wrong.

 

[cyberwolf]

I see a @stoDate but no date field in the INSERT INTO statement. Could this be causing your issues?

Hi all. i'm trying to learn how to write paramterized queries. from what i've pieced together on the internet i've made a few efforts. i'm having trouble though and i'm sure it's something i'm not doing right.

here is my insert code and parameters i'm trying to enter. i have ms access 2003:

insstr = "INSERT INTO downtimetable ( [Mechanic Name], Shift, [Line Number], [Machine Description], [Failure Point], [Cause of Failure], [Estimated Time], [Equipment Number], [WO Status] )" & _
"VALUES (@stoName, @stoDate, @stoShift, @stoLine, @stoMachine, @stoFailure, @stoCause, @stoEstTime, @stoEquipment_Number, @stoWOStatus);"
msgbox insstr, vbOKOnly, "test"
With cmd
    Set .ActiveConnection = conn
    .CommandText = insstr
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("stoName", adVarChar, adParamInput, Len(cboUsername.value), cboUsername.value)
    .Parameters.Append .CreateParameter("stoDate", adDate, adParamInput, Len(txtDate), txtDate)
    .Parameters.Append .CreateParameter("stoShift", adInteger, adParamInput, Len(txtShift), txtShift)
    .Parameters.Append .CreateParameter("stoLine", adVarChar, adParamInput, Len(cboLine.value), cboLine.value)
    .Parameters.Append .CreateParameter("stoMachine", adVarChar, adParamInput, Len(cboMachine.value), cboMachine.value)
    .Parameters.Append .CreateParameter("stoFailure", adVarChar, adParamInput, Len(cboFailure.value), cboFailure.value)
    .Parameters.Append .CreateParameter("stoCause", adVarChar, adParamInput, Len(txtFailure), txtFailure)
    .Parameters.Append .CreateParameter("stoEstTime", adInteger, adParamInput, Len(txtEstTime), txtEstTime)
    .Parameters.Append .CreateParameter("stoEquipment_Number", adInteger, adParamInput, Len(txtEquipment_Number), txtEquipment_Number)
    .Parameters.Append .CreateParameter("stoWOStatus", adVarChar, adParamInput, Len(chkstatus), chkstatus)
End With

after i try and run this the application errors out with a type mismatch. i have tried changing numerous fields around tring different "ad-" types and all come with the same result. my database column id's are what is shown above. i put a msgbox catch in the code right before and right after the with statement. i see the msgbox before but not after. so i'm getting thrown out at the with statement. the code is fine and the insert string looks good up to that point.

all of this code is together in the onclick event of a save button.

What am i doing wrong?

Thanks

 

[bchaney]

@cyberwolf

Sorry about posting that code. I had tried to take out the date variable to rule out my date being in the wrong format (access looks for a date type variable and I could have been converting it into a string). I hurriedly put the code back in to send to the forum. The same result happens with or without the date term in place.

@pbaldy

I didn't know that stored procedures were on the server. I thought stored meant that access held them and then you called the procedure inputing the parameters in while you did that. Now that I know that's what stored procedures are, I'm doing research now trying to properly write stored procedures in mysql and then calling them. There's quite a bit of information to read through. If I don't have any luck with that then I'll post some more with the problem.

I tried the double quote thing and my circumstance calls for the user being able to have double and single quotes together in the string. So, from what I seen on other forums on this and other sites, stored procedures are the way to go. I just didn't know what they were or where to properly place them. Thanks to you guys now i'm pointed in the right direction.

I'll post what I find out.

Thanks

 

[pbaldy]

No problem. Your original code was in the right ballpark, but the text would simply be the name of the stored procedure. By the way, your error may have been due to the cmd variable not having been declared or set properly, unless it happened earlier in code you didn't post. I was trying to clarify the direction we were going in before going into any details. It would look like:

  Dim cmd                     As ADODB.Command

  Set cmd = New ADODB.Command
  With cmd
    .ActiveConnection = "YourConnectionStringHere"
    .CommandText = "StoredProcedureName"
    .CommandType = adCmdStoredProc
    'your parameters here
    .Execute 
  End With

 

[bchaney]

@pbaldy

That last post really helped me. I'm currently doing more research on mysql. I got the code finally to accept but now mysql is throwing back an error saying I don't have command execute privileges. I'm trying to work through that now. When I finally get through this part I'll post everything on here for everyone else who deals with mysql backend with access front end and stored procedures.

Thanks again.

 

[bchaney]

Hey I got it. Here is my code that works:

Dim connstr As String
Dim insstr As String
Dim conn As New ADODB.Connection
Dim cmd As ADODB.Command
Dim equipid As String
 
connstr = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=maintenanceloft;DATABASE=mechanicdatabase;USER=user;PASSWORD=******;OPTION=3;"
 
Set cmd = New ADODB.Command
 
With cmd
    .ActiveConnection = connstr
    .CommandText = "dataentryinsert"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("stoName", adVarChar, adParamInput, Len(cboUsername.value), cboUsername.value)
    .Parameters.Append .CreateParameter("stoDate", adDate, adParamInput, Len(txtDate), txtDate)
    .Parameters.Append .CreateParameter("stoShift", adInteger, adParamInput, Len(txtShift), txtShift)
    .Parameters.Append .CreateParameter("stoLine", adVarChar, adParamInput, Len(cboLine.value), cboLine.value)
    .Parameters.Append .CreateParameter("stoMachine", adVarChar, adParamInput, Len(cboMachine.value), cboMachine.value)
    .Parameters.Append .CreateParameter("stoFailure", adVarChar, adParamInput, Len(cboFailure.value), cboFailure.value)
    .Parameters.Append .CreateParameter("stoCause", adVarChar, adParamInput, Len(txtFailure), txtFailure)
    .Parameters.Append .CreateParameter("stoEstTime", adInteger, adParamInput, Len(txtEstTime), txtEstTime)
    .Parameters.Append .CreateParameter("stoEquipment_Number", adInteger, adParamInput, Len(txtEquipment_Number), txtEquipment_Number)
    .Parameters.Append .CreateParameter("stoWOStatus", adVarChar, adParamInput, Len(chkstatus), chkstatus)
    .Execute
End With

Does this code open and close the connection automatically? I did have conn.close and set conn = nothing at the end but it errored out with "I can't do that because your connection isn't open" error. But nowhere in my code do I open a connection. Just a minor question. Everything is getting inserted correctly, including all different characters like "" and '.

I had to go into the server and set execute privileges. Log in under admin in mysql command line and type:

GRANT (what you want granted) ON yourdb.* TO dbuser @ '(localhost or IP address)' IDENTIFIED BY 'password';

That should grant you the privileges for your application. Just make sure, if you have stored procedures, that you grant the 'EXECUTE' privilege.

I hope that helps someone.

Thanks again pbaldy.