Lesson 101 for SQL Query in VB

There are basically more than one way of doing things in building the query for execution. First is you dump everything into the query string. Things will go wrong when the variables you passing in contain sensitive character like single quote and so on. Single quote will call it an end to the expression prematurely and causing SQL command error. Here comes the first, unsafe, direct way of doing things.

Dim con As OleDbConnection = New OleDbConnection(“connection string bla bla”)

Dim str As String = “SELECT * FROM admin WHERE id = ‘”+ idstr+”‘ AND password = ‘” + passwordstr +”‘ “

Dim cmd As OleDbCommand = New OleDbCommand(str, con)
Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
Dim ds As DataSet = New DataSet
da.Fill(ds, “Record”)
con.Close()

The second way of doing it is to build the query in separate parts. The query string will look like…

Dim str As String = “SELECT * FROM admin WHERE id = ? AND password = ?“

You put question mark instead of concatenating the variable inside it. Then, you will continue the coding as usual, declaring OleDbCommand

Dim cmd As OleDbCommand = New OleDbCommand(str, con)

And before you continue to declare OleDbAdapter, you will initialize OleDbParameter into the OleDbCommand you built earlier.

cmd.Parameters.Add(New OleDbParameter(“user”, userstr))
cmd.Parameters.Add(New OleDbParameter(“pass”, passwordstr))

The final product of the second way of doing tings would be..

Dim con As OleDbConnection = New OleDbConnection(“connection string bla bla”)

Dim str As String = “SELECT * FROM admin WHERE id = ? AND password = ?“

Dim cmd As OleDbCommand = New OleDbCommand(str, con)

cmd.Parameters.Add(New OleDbParameter(“user”, userstr))
cmd.Parameters.Add(New OleDbParameter(“pass”, passwordstr))

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
Dim ds As DataSet = New DataSet
da.Fill(ds, “Record”)
con.Close()

With this safer way, you can throw anything into the expression with no worry for causing SQL command error provided you write everything properly in the first place. Note that you need to declare the OleDbParameter in sequence according to the way it is in the string. So, for this example, the first parameter will go into “id” and the later to “password”

Happy Programming…

Popularity: 1% [?]