Specifying a variable in a sql script in VB

[garyholc]

I have a form with a datagrid and a button on it. I can get the data to fill the datagrid with the sql statement

Dim adapter As New OleDb.OleDbDataAdapter("Select * from table1",cn)

Ok, I add a text box to the form, and call it txtEntryBox. I want to type a name in the txtEntryBox, and get the SQL statement above to search for it. I have come up with:

Dim adapter As New OleDb.OleDbDataAdapter("Select * from table1 where name= ' & strtext & '", cn)

where strtext is a string variable I have declared, and moved the content of the txtEntryBox into it. The variable is moving into strtext as running a msgbox at the end containing the strtext is ok.

However, the sql statement always returns a blank. Its as if it cannot include the content of the txtEntryBox (strtext) in the sql statement, or I am writing it wrong.

Or am I doing it all wrong??? Its been driving me nuts!!!

 

[vis781]

Instead try.....

Dim Adapter As New OleDdDataAdapter
Dim Cmd As New OleDbCommand("SELECT * FROM table1 WHERE name = ?",cn)
cmd.parameters.add("@Name",oledbtype.varwchar,255,"name").value = strtext
adapter.SelectComand = cmd
Adapter.fill(Your dataTable)

 

[garyholc]

Nearly there

Thanks for the reply, but it does not like some of the things you mentioned. Here is the complete code:

Public Class Form1
Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\db1.mdb;User Id=admin;Password=;")
Dim Adapter As New Odbc.OdbcDataAdapter
Dim Cmd As New OleDb.OleDbCommand("SELECT * FROM table1 WHERE name = ?", cn)
Dim ds As New DataSet
Dim strtext As String


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
strtext = Me.txtEntryBox.Text
Cmd.Parameters.Add("@Name", oledbtype.varchar, 255, "name").value = strtext
Adapter.SelectCommand = Cmd
Adapter.Fill(ds)
DataGrid1.DataSource = ds.Tables("Table1")
DataGrid1.Update()
End Sub

End Class

It underlines the word cmd in the line : adapter.selectcommand = cmd in blue saying that value cannot be converted etc.

I'm using Visual Studio 2005 by the way...

 

[vis781]

Substitue...

Dim cmd as new oledbcommand...

with

dim cmd as new odbccommand.....

Just a quick thing though.

Why are you using ODBCconnection and an OLEDBdataadapter

 

[garyholc]

Still no luck

Ok, thanks again, have checked that there are no odbc/oledb differences, have changed them all to oledb, mainly because if I use odbc the variable cmd goes to a blue underline and it brings up errors. The following code does not bring up any errors, but also does not return anything at all, it dosent even fill the datagrid... Not sure if it is running, cant believe this is so difficult, I swear VB didnt used to be this complex when I last used it!!

if you want , completely scrap my code and re-write in your own, like I said, I just need to fill a datagrid with data from a table depending on a name I type in an entrybox on the form...


Public Class Form1
Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\db1.mdb;User Id=admin;Password=;")
Dim Adapter As New OleDb.OleDbDataAdapter
Dim Cmd As New OleDb.OleDbCommand("SELECT * FROM table1 WHERE name = ?", cn)
Dim ds As New DataSet
Dim strtext As String
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
strtext = Me.txtEntryBox.Text
Cmd.Parameters.Add("@Name", OleDb.OleDbType.VarChar, 255, "name").Value = strtext
Adapter.SelectCommand = Cmd
Adapter.Fill(ds)
DataGrid1.DataSource = ds.Tables("Table1")
DataGrid1.Update()
End Sub

End Class


Thanks for all your help.

 

[TechGnome]

Move this:

Dim cn AsNew OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\db1.mdb;User Id=admin;Password=;")
Dim Adapter AsNew OleDb.OleDbDataAdapter
Dim Cmd AsNew OleDb.OleDbCommand("SELECT * FROM table1 WHERE name = ?", cn)
Dim ds AsNew DataSet
Dim strtext AsString

into the sub, like this:

PublicClass Form1
PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cn AsNew OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\db1.mdb;User Id=admin;Password=;")
Dim Adapter AsNew OleDb.OleDbDataAdapter
Dim Cmd AsNew OleDb.OleDbCommand("SELECT * FROM table1 WHERE name = ?", cn)
Dim ds AsNew DataSet
Dim strtext AsString

strtext = Me.txtEntryBox.Text
Cmd.Parameters.Add("@Name", OleDb.OleDbType.VarChar, 255, "name").Value = strtext
Adapter.SelectCommand = Cmd
Adapter.Fill(ds)
DataGrid1.DataSource = ds.Tables("Table1")
DataGrid1.Update()
EndSub

EndClass

-tg

 

[vis781]

Nice one TG, missed that!

 

[garyholc]

Thanks again. Ok while no errors etc. I get nothing back. I have added a msgbox at the end just to ensure it is running the entire code, and to check that a value is assigned to the strtext variable, which it is, and I have checked the name exists in the database. However nothing is returned, my data grid stays blank....

Code now is:

Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\db1.mdb;User Id=admin;Password=;")
Dim Adapter As New OleDb.OleDbDataAdapter
Dim Cmd As New OleDb.OleDbCommand("SELECT * FROM table1 WHERE name = ?", cn)
Dim ds As New DataSet
Dim strtext As String
strtext = Me.txtEntryBox.Text
Cmd.Parameters.Add("@Name", OleDb.OleDbType.VarChar, 255, "name").Value = strtext
Adapter.SelectCommand = Cmd
Adapter.Fill(ds)
DataGrid1.DataSource = ds.Tables("Table1")
DataGrid1.Update()
MsgBox(strtext)
End Sub
End Class

Is there any way of me returning the sql string to see what is being sent to the database?

 

[vis781]

Yea you can display a messagebox with the cmd.commandtext to see if the oledbcommand is ok. But it seems to me that there's someything else going on here. All the code looks ok to me.....
At the adpater.fill line change to this...

Dim i as integer
i = adapter.fill(ds)
messgebox.show(convert.tostring(i))

I just want to see if there are any rows being returned from the database.

 

[garyholc]

Thanks for the reply. Yes it returns a number representing the number of matches it finds in the database - so why dosent it show the data in the datagrid if it is finding the data???????

 

[vis781]

In the following bit, make sure that your datatable is called 'table1' my guess is that it isn't.

DataGrid1.DataSource = ds.Tables("Table1")