Visual Basic&database, SQL Command Drive Me Crazy !!!

SQL Command Drive Me Crazy !!!

Author	Message
Sheppe Phar #1 / 10	SQL Command Drive Me Crazy !!! Quote: >I have a question that made me hang around 2 day, >until now i still can't find the solution to fix it. >Following is brief description of my problem: > data1.RecordSource = "SELECT * FROM student WHERE level = 'Credit'" >Assumetion >---------- >1. "data1" is the data control that already connect to the database >2. "student" is the table that exist inside the database >3. "level" is one of fields that exist inside the student table >From above code, the condition (level = 'Credit') is fixed and can't be >change when the program is running. >How about if i want to allow the user to enter the data then >i use the user entered data to search the specified record ? >data1.RecordSource = "SELECT * FROM student WHERE level = txtLevel.Text" >Assumetion >---------- >1. txtLevel is the text box object that allow user to enter the data >SQL command seem does not allow to contain the dynamic variable in its >condition statement, because i code the above command but its show me the >error message "Too few parameter,expected 1 ". What is it mean? >Hope ppl whose know the solution can kindly to solve my problem, i will >be grateful if u able to help You have to separate your control property from your string, and concatenate them together. IE: data1.RecordSource = "SELECT * FROM student WHERE level = '" _ & txtLevel.Text & "'" Note that the underscore at the end of line one tells the VB IDE to regard the next line as a continuation of the current line, and is not required for string concatenation. You should be able to cut and paste this code. Hope this helps.... Sheppe Pharis, MCP * 3/TekMetrics VB Certified VB5/VB6/VBS/ActiveX Developer
Fri, 19 Apr 2002 03:00:00 GMT

Jack Nickelso #2 / 10	SQL Command Drive Me Crazy !!! Quote: > >I have a question that made me hang around 2 day, > >until now i still can't find the solution to fix it. > >Following is brief description of my problem: > > data1.RecordSource = "SELECT * FROM student WHERE level = 'Credit'" > >Assumetion > >---------- > >1. "data1" is the data control that already connect to the database > >2. "student" is the table that exist inside the database > >3. "level" is one of fields that exist inside the student table > >From above code, the condition (level = 'Credit') is fixed and can't be > >change when the program is running. > >How about if i want to allow the user to enter the data then > >i use the user entered data to search the specified record ? > >data1.RecordSource = "SELECT * FROM student WHERE level = txtLevel.Text" > >Assumetion > >---------- > >1. txtLevel is the text box object that allow user to enter the data > >SQL command seem does not allow to contain the dynamic variable in its > >condition statement, because i code the above command but its show me the > >error message "Too few parameter,expected 1 ". What is it mean? > >Hope ppl whose know the solution can kindly to solve my problem, i will > >be grateful if u able to help > You have to separate your control property from your string, and concatenate > them together. IE: > data1.RecordSource = "SELECT * FROM student WHERE level = '" _ > & txtLevel.Text & "'" > Note that the underscore at the end of line one tells the VB IDE to regard the > next line as a continuation of the current line, and is not required for string > concatenation. You should be able to cut and paste this code. > Hope this helps.... > Sheppe Pharis, MCP * 3/TekMetrics VB Certified > VB5/VB6/VBS/ActiveX Developer What if your input is an integer value? I'm trying to get the following to work: Dim strCriteria As String Dim TheCartonStr As String Dim theLotNum As Integer strCriteria = "[Carton] = '" & TheCartonStr & "'" & " And "[LotID] = 'theLotNum"'" Carton is a string field in a table, LotID is an Integer field in the same table. I can get it to work for Carton, but not for LotID. Thanks Jack
Fri, 19 Apr 2002 03:00:00 GMT

Stephen Smit #3 / 10	SQL Command Drive Me Crazy !!! Quote: > What if your input is an integer value? > I'm trying to get the following to work: > Dim strCriteria As String > Dim TheCartonStr As String > Dim theLotNum As Integer > strCriteria = "[Carton] = '" & TheCartonStr & "'" & " And "[LotID] = > 'theLotNum"'" A few too many quotes in there you know. Build the SQL as you would build one for a MsgBox or a label: strCriteria = "[Carton] = '" & TheCartonStr & "' And [LotID] = " & theLotNum ...so that strCriteria gets a string something like: "[Carton] = 'MyCarton' And [LotID] = 1" Quote: > Carton is a string field in a table, LotID is an Integer field in the same > table. > I can get it to work for Carton, but not for LotID. > Thanks > Jack No worries. Steve.
Fri, 19 Apr 2002 03:00:00 GMT

Joe #4 / 10	SQL Command Drive Me Crazy !!! Geez, you must have the hardest time ordering anything over the phone... Quote: > What if your input is an integer value? > I'm trying to get the following to work: > Dim strCriteria As String > Dim TheCartonStr As String > Dim theLotNum As Integer > strCriteria = "[Carton] = '" & TheCartonStr & "'" & " And "[LotID] = > 'theLotNum"'" > Carton is a string field in a table, LotID is an Integer field in the same > table. > I can get it to work for Carton, but not for LotID. Leave off the tickmarks for numbers. Easier yet, use parameterized queries. This is for DAO, but I imagine ADO's equivalent will be quite similar: dim qd as querydef: set qd=mydb.createquerydef("") qd.sql = "parameters pCarton text, pLot long;" _ & " select * from foo where carton = pcarton and lotid = plot" qd!pcarton = TheCartonStr qd!plot = theLotNum dim rs as recordset: set rs=qd.openrecordset() Otherwise, you get to hassle with Str$ and Format to make sure embedded numbers and datetimes are in the American style the SQL parsers expect and Replace to make sure embedded quotes and pipe characters within text values are properly escaped. -- WARNING: I cannot be held responsible for the above They're coming to because my cats have apparently learned to type. take me away, ha ha!
Fri, 19 Apr 2002 03:00:00 GMT

JoJo #5 / 10	SQL Command Drive Me Crazy !!! I have a question that made me hang around 2 day, until now i still can't find the solution to fix it. Following is brief description of my problem: data1.RecordSource = "SELECT * FROM student WHERE level = 'Credit'" Assumetion ---------- 1. "data1" is the data control that already connect to the database 2. "student" is the table that exist inside the database 3. "level" is one of fields that exist inside the student table From above code, the condition (level = 'Credit') is fixed and can't be change when the program is running. How about if i want to allow the user to enter the data then i use the user entered data to search the specified record ? data1.RecordSource = "SELECT * FROM student WHERE level = txtLevel.Text" Assumetion ---------- 1. txtLevel is the text box object that allow user to enter the data SQL command seem does not allow to contain the dynamic variable in its condition statement, because i code the above command but its show me the error message "Too few parameter,expected 1 ". What is it mean? Hope ppl whose know the solution can kindly to solve my problem, i will be grateful if u able to help
Sat, 20 Apr 2002 03:00:00 GMT

JoJo #6 / 10	SQL Command Drive Me Crazy !!! I have a question that made me hang around 2 day, until now i still can't find the solution to fix it. Following is brief description of my problem: data1.RecordSource = "SELECT * FROM student WHERE level = 'Credit'" Assumetion ---------- 1. "data1" is the data control that already connect to the database 2. "student" is the table that exist inside the database 3. "level" is one of fields that exist inside the student table From above code, the condition (level = 'Credit') is fixed and can't be change when the program is running. How about if i want to allow the user to enter the data then i use the user entered data to search the specified record ? data1.RecordSource = "SELECT * FROM student WHERE level = txtLevel.Text" Assumetion ---------- 1. txtLevel is the text box object that allow user to enter the data SQL command seem does not allow to contain the dynamic variable in its condition statement, because i code the above command but its show me the error message "Too few parameter,expected 1 ". What is it mean? Hope ppl whose know the solution can kindly to solve my problem, i will be grateful if u able to help
Sat, 20 Apr 2002 03:00:00 GMT

Ian Pidgeo #7 / 10	SQL Command Drive Me Crazy !!! You don't need quotes either side of the Lot number if it is a number, but you do need to convert it to a string. Dim TheCatonStr as String Dim theLotNum as Integer TheCartonStr="Big Carton" theLotNum=1025 strCriteria = "Carton = '" & TheCartonStr & "' And LotID = " & Str(theLotNum) This evaluates to: strCriteria = "Carton = 'Big Carton' And LotID = 1025" Regards, Ian Quote: > What if your input is an integer value? > I'm trying to get the following to work: > Dim strCriteria As String > Dim TheCartonStr As String > Dim theLotNum As Integer > strCriteria = "[Carton] = '" & TheCartonStr & "'" & " And "[LotID] = > 'theLotNum"'" > Carton is a string field in a table, LotID is an Integer field in the same > table. > I can get it to work for Carton, but not for LotID. > Thanks > Jack
Sat, 20 Apr 2002 03:00:00 GMT

Jack Nickelso #8 / 10	SQL Command Drive Me Crazy !!! Thank you all very much!!!! Quote: > > What if your input is an integer value? > > I'm trying to get the following to work: > > Dim strCriteria As String > > Dim TheCartonStr As String > > Dim theLotNum As Integer > > strCriteria = "[Carton] = '" & TheCartonStr & "'" & " And "[LotID] = > > 'theLotNum"'" > A few too many quotes in there you know. > Build the SQL as you would build one for a MsgBox or a label: > strCriteria = "[Carton] = '" & TheCartonStr & "' And [LotID] = " & theLotNum > ...so that strCriteria gets a string something like: > "[Carton] = 'MyCarton' And [LotID] = 1" > > Carton is a string field in a table, LotID is an Integer field in the same > > table. > > I can get it to work for Carton, but not for LotID. > > Thanks > > Jack > No worries. > Steve.
Sun, 21 Apr 2002 03:00:00 GMT

Gabriele Vaccher #9 / 10	SQL Command Drive Me Crazy !!! Quote: >I have a question that made me hang around 2 day, >until now i still can't find the solution to fix it. >Following is brief description of my problem: > data1.RecordSource = "SELECT * FROM student WHERE level = 'Credit'" >Assumetion >---------- >1. "data1" is the data control that already connect to the database >2. "student" is the table that exist inside the database >3. "level" is one of fields that exist inside the student table >From above code, the condition (level = 'Credit') is fixed and can't be >change when the program is running. >How about if i want to allow the user to enter the data then >i use the user entered data to search the specified record ? >data1.RecordSource = "SELECT * FROM student WHERE level = txtLevel.Text" Try: data1.RecordSource = ("SELECT * FROM student WHERE level = ' " & txtLevel.Text & " ' )
Thu, 25 Apr 2002 03:00:00 GMT

Page 1 of 1

[ 10 post ]