Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB.NET 1.0 > VB.NET 2002/2003 Basics
| Search | Today's Posts | Mark Forums Read
VB.NET 2002/2003 Basics For coders who are new to Visual Basic, working in .NET versions 2002 or 2003 (1.0 and 1.1).
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB.NET 2002/2003 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 13th, 2007, 08:46 PM
Authorized User
 
Join Date: Dec 2006
Location: Tucson, AZ, USA.
Posts: 43
Thanks: 4
Thanked 0 Times in 0 Posts
Send a message via MSN to Yasho
Default Select statement with where clause with 2 conditon

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
__________________
yvk
  #2 (permalink)  
Old May 13th, 2007, 09:12 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
  #3 (permalink)  
Old May 14th, 2007, 01:50 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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.
  #4 (permalink)  
Old May 14th, 2007, 01:55 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
  #5 (permalink)  
Old May 14th, 2007, 07:50 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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
  #6 (permalink)  
Old May 15th, 2007, 11:54 PM
Authorized User
 
Join Date: Dec 2006
Location: Tucson, AZ, USA.
Posts: 43
Thanks: 4
Thanked 0 Times in 0 Posts
Send a message via MSN to Yasho
Default

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
  #7 (permalink)  
Old May 16th, 2007, 06:43 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
  #8 (permalink)  
Old May 16th, 2007, 01:40 PM
Authorized User
 
Join Date: Dec 2006
Location: Tucson, AZ, USA.
Posts: 43
Thanks: 4
Thanked 0 Times in 0 Posts
Send a message via MSN to Yasho
Default

Mr. D Parsons,

I thank you again

Yasho


yvk


Similar Threads
Thread Thread Starter Forum Replies Last Post
Case Statement In Where Clause Logic dwj119 SQL Server 2000 4 October 27th, 2011 03:14 PM
If statement in WHERE clause frakey Classic ASP Basics 30 July 8th, 2008 03:35 PM
Select statement with where clause for visual basi Yasho SQL Language 6 May 16th, 2007 01:00 AM
If statement in Where Clause jdziggy SQL Server 2000 2 March 16th, 2007 08:08 AM
USING SQL IF Statement in WHERE clause rit01 SQL Server 2000 17 March 15th, 2007 08:33 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.