Pass a parameter in sql statement with VB 2005...

  • Thread starter Thread starter bill
  • Start date Start date
B

bill

Can someone please show me an example of passing a string value into an sql
statement in vb 2005? Something like this is what I'm after:
Dim sqlButton1 As String = "Select * from tblAssets where Asset_Tag =
Me.cboAsset.Text"

Thank you,

Bill
 
I beleive this is what you are looking for (did some googling)-

Take a look at this link:
http://www.java2s.com/Code/VB/Database-ADO.net/PassParameterintoSQLcommand.htm

and look at the line that says:
cmd.Parameters.Add(New SqlParameter("@fn", SqlDbType.VarChar, 10)).Value =
"Joe"

take note of the @fn which is in the line above:
Dim cmd As New SqlCommand("SELECT FirstName, LastName FROM Employee WHERE
FirstName = @fn", con)

you DO NOT want to do

"Select * from Employee where FirstName = " + Text1.Text

You might be using a Combo Box. If your combo box is generated by you, then
you are ok. But if the user generates the data within the combo box - then
be careful....

because of SQL injections.
Skim this article:
http://www.sitepoint.com/article/sql-injection-attacks-safe ( at page 2 you
will see the basic reason )
or by the middle of this article:
http://blog.colinmackay.net/archive/2007/06/24/77.aspx

basically someone can execute sql within your sql and change your data /
bypass your security / delete your data.


Hope this helps.

Miro
 
Thank you for your reply. Can you explain to me what this is since it
doesn't apprear to be an assigned variable name? I haven't seen this
before. "@fn"
Thank you!
Bill
 
It is your own variable / parameter holder ( as long as it starts with the
@ ) symbol.

You can name it @bill
If you have multiple parameters then they all must be unique in the
statement.

example: Select * from @bla where @bill = @miro

therefore It would expect me to add 3 parameters via the cmd.Parameters.Add

one for @bla, one for @bill and one for @miro

Miro
 
Excellent! Thank you very much for taking the time to explain. I didn't
see it declared and was thinking maybe it was some kind of new built in
function. Thank you!
Bill
 
Can this be used with MS access by just changing the connection string or
are they only SQL server specific?
 
Ya, I need adodb so this probably won't work with an access database right?
I've been using dataTables up until now.
 
I'm thinking something like this but I get stuck:
Dim Con = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;" &
"data source=c:\_Archive\Documentation - Projects\Hardware Tracking -
2008\IT_Assets.mdb")

Dim cmd As New OleDb.OleDbCommand("SELECT FirstName, LastName FROM Employee
WHERE FirstName = @fn", Con)

cmd.Parameters.Add(New SqlParameter("@fn", SqlDbType.VarChar, 10)).Value =
"Joe"
 
When using an OleDbCommand you should not use a SqlParmeter, as that
is for SQL Server. Use OleParameter instead.

How are you stuck?
 
This is what I've got so far...thank you. I get stuck on the line before
the "Try" line. Cmd.Parameters.Add(New
oldDB.oldDBParameter("@fn",oledb.??????????
I think the rest is fine?
Dim Con = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;" &
"data source=c:\_Archive\Documentation - Projects\Hardware Tracking -
2008\IT_Assets.mdb")

Dim Cmd As New OleDb.OleDbCommand("SELECT * from tblAssets where asset_tag =
@fn", Con)

Cmd.Parameters.Add(New OleDb.OleDbParameter("@fn",oledb.oel)

Try

Con.Open()

Dim reader As OleDb.OleDbDataReader = Cmd.ExecuteReader()

While reader.Read()

Console.WriteLine("{0} - {1}", reader.GetString(0), reader.GetString(1))

End While

reader.Close()

Finally

Con.Close()

End Try
 
I think this is it:
'Dim Con = New SqlConnection("Server=(local)\SQLEXPRESS;Initial
Catalog=MyDatabase;Integrated Security=SSPI")

Dim Con = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;" &
"data source=c:\_Archive\Documentation - Projects\Hardware Tracking -
2008\IT_Assets.mdb")

'Dim cmd As New SqlCommand("SELECT FirstName, LastName FROM Employee WHERE
FirstName = @fn", con)

Dim Cmd As New OleDb.OleDbCommand("SELECT * from tblAssets where asset_tag =
@fn", Con)

'cmd.Parameters.Add(New SqlParameter("@fn", SqlDbType.VarChar, 10)).Value =
"Joe"

Cmd.Parameters.Add(New OleDb.OleDbParameter("@fn", OleDb.OleDbType.VarChar,
30)).Value = "Joe"

Try

Con.Open()

Dim reader As OleDb.OleDbDataReader = Cmd.ExecuteReader()

While reader.Read()

Console.WriteLine("{0} - {1}", reader.GetString(0), reader.GetString(1))

End While

reader.Close()

Finally

Con.Close()

End Try



Does that look correct?
 
I'm not sure what this line means:
'Console.WriteLine("{0} - {1}", reader.GetString(0), reader.GetString(1))

So to assign say a dataview grid to this set of records is it just

Me.DataGridView1.DataSource = reader because I don't get anything back with
that?
 
I'm not sure what this line means:
'Console.WriteLine("{0} - {1}", reader.GetString(0), reader.GetString(1))
Click to expand...

Console.WriteLine takes a format string. See String.Format for more
information. The numbers in braces are parameter numbers. In the
code above, {0} is replaced by the next parameter, reader.GetString(0)
(the value of column 0) and {1} by reader.GetString(1) (the value of
column 1).
So to assign say a dataview grid to this set of records is it just

Me.DataGridView1.DataSource = reader because I don't get anything back with
that?
Click to expand...

No. A DataReader supplies one row each time. For more information on
this see
<http://msdn.microsoft.com/en-us/library/haa3afyz(VS.71).aspx>.

The DataGridView.DataSource property takes some kind of list. You
must iterate through the DataReader and populate some kind of list. A
good one to use is BindingList(Of T), as that supplies a lot of
functionality that is useful when binding controls to a list.

However since you are just getting started with VB .NET, it might be
easier for you to create a DataSet from the DataReader using a
DataAdapter, and bind the DataGridView to the DataSet's DataTable.
Here is some information about this
<http://msdn.microsoft.com/en-us/library/bh8kx08z.aspx>

By using Google you should be able to find more examples.

Also, it is probably not necessary to specify the data type on the
Parameters.Add call. I know it is not necessary with SQL Server, but
I'm not sure about OleDB. You probably can just use:

Cmd.Parameters.Add(New OleDb.OleDbParameter("@fn", "Joe"))
 
Back
Top