Problem in SQL statement in VB.NET.. Assistance Required.

Question

kavithabhaskar 0 Junior Poster

16 Years Ago

Can you please tell me what's wrong with this statement ? i get no errors but I get no o/p either!

Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Copy of USERS.mdb")
If (ComboBox6.SelectedItem = 0.5) And (ComboBox7.SelectedItem) Then
Dim cmd1 As New OleDbCommand
cmd1 = New OleDbCommand("select * from table1 where RAM between 0.5000 and 2.000")
End If
End Sub

vb.net

2 Contributors
18 Replies
122 Views
4 Days Discussion Span
Latest Post 16 Years Ago Latest Post by RipperJT

RipperJT 2 Newbie Poster

16 Years Ago

Hi,

You specified the connection and command objects but you probably want a data reader...also you'll need to open the connection.

Dim dr As OleDbDataReader
dr = cmd1.ExecuteReader(CommandBehavior.Default)

Do while dr.read()
variablename = dr("columnName")

loop

RipperJT 2 Newbie Poster

16 Years Ago

It's trying to convert the datatype on the left side to the empty string on the right side of the =. Try...

If (ComboBox6.SelectedItem = 0) And (ComboBox7.SelectedItem = 0) Then

RipperJT 2 Newbie Poster

16 Years Ago

Do you have real values selected in the combobox6 & 7 when the click event is fired or does it only fail when you haven't specified any values? If there isn't any values select then you should not run the query.

RipperJT 2 Newbie Poster

16 Years Ago

You can try to test to see if a value has been entered and if so then run your code.

If IsNumeric(ComboBox6.SelectedItem) = True And IsNumeric(ComboBox7.SelectedItem) = True Then
            If ComboBox7.SelectedItem > ComboBox6.SelectedItem Then

                'code here
		
		Else
		
		'Msgbox("Ram should be selected")
		Exit Sub

            End If

        Else
            'exit the sub 
            'MsgBox("blah")

            Exit Sub

        End If

Here's info an the function IsNumeric

http://msdn.microsoft.com/en-us/library/6cd3f6w1(VS.71).aspx

RipperJT 2 Newbie Poster

16 Years Ago

Basically what you want to do is determine if valid values exist before trying to run the query. I haven't tried this out so please test...

Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Copy of USERS.mdb")

Dim cmd1 As New OleDbCommand

cmd1 = New OleDbCommand("select * from table1 where RAM between '& ComboBox6.SelectedItem & ' and '& ComboBox7.SelectedItem & '", con)

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd1)

Dim ds As DataSet = New DataSet()

 If IsNumeric(ComboBox6.SelectedItem) = True And IsNumeric(ComboBox7.SelectedItem) = True Then
            If ComboBox7.SelectedItem > ComboBox6.SelectedItem Then

 	  
		Try


		da.Fill(ds, "table1")

		DataGridView1.DataSource = ds.Tables("table1").DefaultView

		Finally

		con.Close()

		cmd1 = Nothing

		da.Dispose()

		con.Dispose()

		End Try

		
		Else
		
		'Msgbox("Ram should be selected")
		Exit Sub

            End If

        Else
            'exit the sub 
            'MsgBox("blah")

            Exit Sub

        End If If IsNumeric(ComboBox6.SelectedItem) = True And IsNumeric(ComboBox7.SelectedItem) = True Then
            If ComboBox7.SelectedItem > ComboBox6.SelectedItem Then

                'code here
		
		Else
		
		'Msgbox("Ram should be selected")
		Exit Sub

            End If

        Else
            'exit the sub 
            'MsgBox("blah")

            Exit Sub

        End If

End Sub

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

kavithabhaskar 0 Junior Poster · Answer 1 · 2008-07-22T18:51:20+00:00

Hi Ripper thanks for ur reply..

the error that i get is

"Conversion from string "" to type 'Double' is not valid." at the line

"If (ComboBox6.SelectedItem = "") And (ComboBox7.SelectedItem = "") Then"

please help me rectify this problem!!

Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click

Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Copy of USERS.mdb")

Dim cmd1 As New OleDbCommand

If (ComboBox6.SelectedItem = "") And (ComboBox7.SelectedItem = "") Then

MsgBox("No Values have been entered ! ")

End If

cmd1 = New OleDbCommand("select * from table1 where RAM between '%" & ComboBox6.SelectedItem & "%' and '%" & ComboBox7.SelectedItem & "%'", con)

End Sub

--------------------------------------------------------------------------------
KB - Knowledge is Power!

kavithabhaskar 0 Junior Poster · Answer 2 · 2008-07-22T20:27:09+00:00

It's trying to convert the datatype on the left side to the empty string on the right side of the =. Try...
If (ComboBox6.SelectedItem = 0) And (ComboBox7.SelectedItem = 0) Then

just curious..what does that statement do ?

kavithabhaskar 0 Junior Poster · Answer 3 · 2008-07-23T00:59:40+00:00

I get the error in the "if" statement. please take a look at it..

if i hit the "click" button directly without choosing the RAM values I expect the Msg box to pop up ..instead i get an error.. ! pls see code for error../

Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Copy of USERS.mdb")
Dim cmd1 As New OleDbCommand
If (ComboBox6.SelectedItem = " ") And (ComboBox7.SelectedItem = " ") Then //error:"conversion from string"" to 'double' is not valid"
MsgBox("No Values have been entered !")
End If
cmd1 = New OleDbCommand("select * from table1 where RAM between '& ComboBox6.SelectedItem & ' and '& ComboBox7.SelectedItem & '", con)
Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd1)
Try
Dim ds As DataSet = New DataSet()
da.Fill(ds, "table1") //error : data type mismatch in criteria expression
DataGridView1.DataSource = ds.Tables("table1").DefaultView
Finally
con.Close()
cmd1 = Nothing
da.Dispose()
con.Dispose()
End Try
End Sub

kavithabhaskar 0 Junior Poster · Answer 4 · 2008-07-23T01:01:46+00:00

Data type mismatch in criteria expression.is the error that i get at line da.Fill(ds, "table1") if i modified the code as below (after removing the"if" statement"

Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Copy of USERS.mdb")
Dim cmd1 As New OleDbCommand
cmd1 = New OleDbCommand("select * from table1 where RAM between '& ComboBox6.SelectedItem & ' and '& ComboBox7.SelectedItem & '", con)
Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd1)
Try
Dim ds As DataSet = New DataSet()
da.Fill(ds, "table1")
DataGridView1.DataSource = ds.Tables("table1").DefaultView
Finally
con.Close()
cmd1 = Nothing
da.Dispose()
con.Dispose()
End Try
End Sub

kavithabhaskar 0 Junior Poster · Answer 5 · 2008-07-24T00:41:13+00:00

hi ripper..either way i get the same error on da.fill(ds,"table1"). that is..when i dont choose any value from either combobox and hit the click and when i choose 1 value from each of the 2 combo boxes and hit click..

yesboth my combo boxes have numbers like.2.980, 3.000,0.5000,3.000,1.000,2.000,3.250 in each of them..

pls suggest something..i have been stuck on this for weeks now..

kavithabhaskar 0 Junior Poster · Answer 6 · 2008-07-24T02:03:36+00:00

Ripper:

but apart from being numeric..my data also has decimals..i am not sure if isnumeric would work in that case..
also..i m not comparing the value in the combobox6 and 7. i want all the values equal to and between them..

You can try to test to see if a value has been entered and if so then run your code.
If IsNumeric(ComboBox6.SelectedItem) = True And IsNumeric(ComboBox7.SelectedItem) = True Then
            If ComboBox7.SelectedItem > ComboBox6.SelectedItem Then

                'code here
		
		Else
		
		'Msgbox("Ram should be selected")
		Exit Sub

            End If

        Else
            'exit the sub 
            'MsgBox("blah")

            Exit Sub

        End If
Here's info an the function IsNumeric
http://msdn.microsoft.com/en-us/library/6cd3f6w1(VS.71).aspx

kavithabhaskar 0 Junior Poster · Answer 7 · 2008-07-24T02:07:40+00:00

Ripper..

i read the MSFT link that u sent to me..where it says that isnumeric is applicable for decimals also..i tried it and the code is below but I still get the same error..! :(

Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Copy of USERS.mdb")
Dim cmd1 As New OleDbCommand
cmd1 = New OleDbCommand("select * from table1 where RAM between '" & IsNumeric(ComboBox6.SelectedItem) & "' and '" & IsNumeric(ComboBox7.SelectedItem) & "'", con)
Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd1)
Try
Dim ds As DataSet = New DataSet()
da.Fill(ds, "table1")
DataGridView1.DataSource = ds.Tables("table1").DefaultView
Finally
con.Close()
cmd1 = Nothing
da.Dispose()
con.Dispose()
End Try
End Sub

RipperJT 2 Newbie Poster · Answer 8 · 2008-07-24T02:23:14+00:00

I pasted too much try this...

Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Copy of USERS.mdb")

Dim cmd1 As New OleDbCommand

cmd1 = New OleDbCommand("select * from table1 where RAM between '& ComboBox6.SelectedItem & ' and '& ComboBox7.SelectedItem & '", con)

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd1)

Dim ds As DataSet = New DataSet()

 If IsNumeric(ComboBox6.SelectedItem) = True And IsNumeric(ComboBox7.SelectedItem) = True Then
            If ComboBox7.SelectedItem > ComboBox6.SelectedItem Then

 	  
		Try


		da.Fill(ds, "table1")

		DataGridView1.DataSource = ds.Tables("table1").DefaultView

		Finally

		con.Close()

		cmd1 = Nothing

		da.Dispose()

		con.Dispose()

		End Try

		
		Else
		
		'Msgbox("Ram should be selected")
		Exit Sub

            End If

        Else
            'exit the sub 
            'MsgBox("blah")

            Exit Sub

	End If

	Exit Sub

kavithabhaskar 0 Junior Poster · Answer 9 · 2008-07-24T21:55:15+00:00

Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Copy of USERS.mdb")
Dim cmd1 As New OleDbCommand
cmd1 = New OleDbCommand("select * from table1 where RAM between '" & CDbl(ComboBox6.SelectedItem) & "' and '" & CDbl(ComboBox7.SelectedItem) & "'", con)
Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd1)
Try
Dim ds As DataSet = New DataSet()
da.Fill(ds, "table1")
DataGridView1.DataSource = ds.Tables("table1").DefaultView
Finally
con.Close()
cmd1 = Nothing
da.Dispose()
con.Dispose()
End Try
End Sub

I get error : "data type mismatch in criteria expression" at line da.fill(ds,"table1").. i am sure its a datatype mismatch due to decimal.. but I am not sure how to rectify this!!

kavithabhaskar 0 Junior Poster · Answer 10 · 2008-07-24T22:27:19+00:00

Ripper:

I am sure that its something to do with data type but i am not able to figure out..!

Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Copy of USERS.mdb")
Dim cmd1 As New OleDbCommand
cmd1 = New OleDbCommand("select * from table1 where RAM between '" & CDec(ComboBox6.SelectedItem) & "' and '" & CDec(ComboBox7.SelectedItem) & "'", con)
Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd1)
Try
Dim ds As DataSet = New DataSet()
da.Fill(ds, "table1")
DataGridView1.DataSource = ds.Tables("table1").DefaultView
Finally
con.Close()
cmd1 = Nothing
da.Dispose()
con.Dispose()
End Try
End Sub

RipperJT 2 Newbie Poster · Answer 11 · 2008-07-24T23:49:49+00:00

I believe the problem is that the query is being run without valid values being assigned to the comboboxes. It's probably trying to compare the Ram columns decimal data type to a empty

Have you tried this code...

Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Copy of USERS.mdb")

Dim cmd1 As New OleDbCommand

cmd1 = New OleDbCommand("select * from table1 where RAM between '& ComboBox6.SelectedItem & ' and '& ComboBox7.SelectedItem & '", con)

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd1)

Dim ds As DataSet = New DataSet()

 If IsNumeric(ComboBox6.SelectedItem) = True And IsNumeric(ComboBox7.SelectedItem) = True Then
            If ComboBox7.SelectedItem > ComboBox6.SelectedItem Then

 	  
		Try


		da.Fill(ds, "table1")

		DataGridView1.DataSource = ds.Tables("table1").DefaultView

		Finally

		con.Close()

		cmd1 = Nothing

		da.Dispose()

		con.Dispose()

		End Try

		
		Else
		
		'Msgbox("Ram should be selected")
		Exit Sub

            End If

        Else
            'exit the sub 
            'MsgBox("Ram should be selected")

            Exit Sub

	End If

	End Sub

kavithabhaskar 0 Junior Poster · Answer 12 · 2008-07-25T00:11:40+00:00

Hi Ripper ..

i tried ur code but i still get the same error on the same line

kb..

I believe the problem is that the query is being run without valid values being assigned to the comboboxes. It's probably trying to compare the Ram columns decimal data type to a empty

Have you tried this code...

Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Copy of USERS.mdb")

Dim cmd1 As New OleDbCommand

cmd1 = New OleDbCommand("select * from table1 where RAM between '& ComboBox6.SelectedItem & ' and '& ComboBox7.SelectedItem & '", con)

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd1)

Dim ds As DataSet = New DataSet()

 If IsNumeric(ComboBox6.SelectedItem) = True And IsNumeric(ComboBox7.SelectedItem) = True Then
            If ComboBox7.SelectedItem > ComboBox6.SelectedItem Then

 	  
		Try


		da.Fill(ds, "table1")

		DataGridView1.DataSource = ds.Tables("table1").DefaultView

		Finally

		con.Close()

		cmd1 = Nothing

		da.Dispose()

		con.Dispose()

		End Try

		
		Else
		
		'Msgbox("Ram should be selected")
		Exit Sub

            End If

        Else
            'exit the sub 
            'MsgBox("Ram should be selected")

            Exit Sub

	End If

	End Sub

RipperJT 2 Newbie Poster · Answer 13 · 2008-07-26T11:16:57+00:00

Sorry for the late reply, I've been pretty busy at work. I've pasted some code below I had some single quotes that should have been double quotes in the previous code. I've tested the code on my system and It pulls the data properly.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        'Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Copy of USERS.mdb")
        

        Dim cmd1 As New OleDbCommand

        cmd1 = New OleDbCommand("select * from table1 where RAM between " & ComboBox6.SelectedItem & " and " & ComboBox7.SelectedItem, con)

        Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd1)

        Dim ds As DataSet = New ramDataSet()

        If IsNumeric(ComboBox6.SelectedItem) = True And IsNumeric(ComboBox7.SelectedItem) = True Then
            If ComboBox7.SelectedItem > ComboBox6.SelectedItem Then


                Try


                    da.Fill(ds, "TABLE1")

                    DataGridView1.DataSource = ds.Tables("TABLE1").DefaultView

                Finally

                    con.Close()

                    cmd1 = Nothing

                    da.Dispose()

                    con.Dispose()

                End Try


            Else

                MsgBox("Ram should be selected")
                Exit Sub

            End If

        Else
            'exit the sub 
            MsgBox("Ram should be selected")

            Exit Sub

        End If