Select statement with where clause with 2 conditon

Yasho · May 13th, 2007, 08:46 PM

I am using vb.net 2003
I have created connection to access database using Microsoft jet 4.0 OLE DB provider
Created a data adapter daCust
Generated dataset dsCust1
Data loads successfully

My program runs fine with one conditon in where clause
but do not run when I put the second conditon.

[u]I get the following error</u>
An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll

Additional information: Cast from string "Select LastName,MI, Address,Stat" to type 'Long' is not valid.

My code

Dim mySearchString As String

        mySearchString = "Select LastName,MI, Address,State, ZipCode, Phone from tblCustomerInfo where FirstName=" & txtFirstName.Text And "city = " & txtcity.Text
            DsCust1.Clear()
            daCust.SelectCommand.CommandText = mySearchString

                dgrCustomers.Refresh()
                daCust.Fill(DsCust1)

    End Sub

yvk

dparsons · May 13th, 2007, 09:12 PM

You didnt terminate the SQL String correctly.

Try this:

"Select LastName,MI, Address,State, ZipCode, Phone from tblCustomerInfo where FirstName='" & txtFirstName.Text & "' And city = '" & txtcity.Text & "'"

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

BrianWren · May 14th, 2007, 01:50 PM

Initially I though that you too delimited the strings wrong . . .
But then I found the single-quotes hiding in the string.
I've made it fixed-space font and bold-face and red to help yvk see the contents.

Code:

"SELECT LastName,MI, Address,State, ZipCode, Phone " & _
"FROM   tblCustomerInfo " & _
"WHERE  FirstName = '" & txtFirstName.Text & "' " & _
"  AND  city      = '" & txtcity.Text & "'"

yvk,
The resultant SQL must have a format that establishes that the values are literal strings. That is the purpose of the single-quotes.

dparsons · May 14th, 2007, 01:55 PM

Ya that is a little better to read ;]

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

planoie · May 14th, 2007, 07:50 PM

Another way to write it that also is more efficient and readable:

string.Format("SELECT LastName,MI, Address,State, ZipCode, Phone FROM tblCustomerInfo WHERE FirstName='{0}' AND city='{1}'", txtFirstName.Text, txtcity.Text);

Ideally, you should use the command object's parameters collection so that you don't have to worry about SQL injection risks.

-Peter

Yasho · May 15th, 2007, 11:54 PM

Thank for the response I received from you all.

But I am greedy to learn more so I have come with few more questions

Q1- what if we substitute "city" to "sDate"(Date and time format) and between txtStartDate.text and txtEndDate.text

Q2-if we substitute "City" to ID and ID being a number format

Q3- Some recommendations for books which cover these type of things

yvk

dparsons · May 16th, 2007, 06:43 AM

You add dates to SQL statements just as you would a regular string. (e.g. '01/01/1900')

Code:

"SELECT LastName,MI, Address,State, ZipCode, Phone " & _
"FROM   tblCustomerInfo " & _
"WHERE  FirstName = '" & txtFirstName.Text & "' " & _
"  AND  DateColume      = '" & sDate & "'"

Number values do not need to be enclosed in ''

Code:

"SELECT LastName,MI, Address,State, ZipCode, Phone " & _
"FROM   tblCustomerInfo " & _
"WHERE  FirstName = '" & txtFirstName.Text & "' " & _
"  AND  id     = " & iID

And I can not answer the last question, however, Wrox does publish quite a few SQL books so you might want to take a look and see what they have.

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

Yasho · May 16th, 2007, 01:40 PM

Mr. D Parsons,

I thank you again

Yasho

yvk