Include SQL Code in VB.Net

[TedN]

This is a pretty basic question so you'll see how far down I am on the learning curve.

 

I'm building a sql string and the whole thing is getting rather unwieldy. So I'm wondering if there is a way to insert sql code not as a string but as a separate sub routine or code section.

 

I can then build the string within the sql sub routine. If this is possible how does one define it as sql code and not vb.

 

 

Thanks,

Ted

[PlausiblyDamp]

If your database supports stored procedures then they are definately an option here. If your DB doesn't (or you choose not to use them) then parameterised queries are probably what you are after - search these forums for 'Parameterised Queries' and you'll find several hits.

 

Some quick ones are

http://www.xtremedotnettalk.com/showthread.php?t=87082 and http://www.xtremedotnettalk.com/showthread.php?t=84159

[TedN]

If your database supports stored procedures then they are definately an option here. If your DB doesn't (or you choose not to use them) then parameterised queries are probably what you are after - search these forums for 'Parameterised Queries' and you'll find several hits.

 

Some quick ones are

http://www.xtremedotnettalk.com/showthread.php?t=87082 and http://www.xtremedotnettalk.com/showthread.php?t=84159

 

 

 

Thanks for the info. I checked the links you provided and have looked up various sites for sql parameters.

 

I'm still struggling a bit though to get parameters to work and would appreciate it if you could tell me where I'm going wrong.

Following is some basic code that should select names from a database if the name in third column is "Smith".

 

   Private Sub btnLoad_Click(ByVal sender As System.Object, _
   ByVal e As System.EventArgs) Handles btnLoad.Click

       Dim con As New OleDb.OleDbConnection
       Dim ds As New DataSet
       Dim da As OleDb.OleDbDataAdapter
       Dim sql As String = ""

       con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
       & "Data Source = C:\AddressBook.mdb"

       con.Open()
       Dim cmd As New OleDb.OleDbCommand("Select * from tblContacts where Column2 = ?", con)

       [b]cmd.Parameters(0).Value = "Smith"[/b]

       [i]da = New OleDb.OleDbDataAdapter(sql, con)[/i]

       da.Fill(ds, "Test")
       con.Close()

       txtFirstItem.Text = ds.Tables("Test").Rows(0).Item(1)
       txtSecondItem.Text = ds.Tables("Test").Rows(0).Item(2)


   End Sub

 

The error I get is: (Bolded line)

"Additional information: Invalid index 0 for this OleDbParameterCollection with Count=0."

 

Also, what do I do with DataAdapter line (Italics) if I no longer have the sql string.

 

Thanks,

Ted

[amir100]

The error I get is: (Bolded line)

"Additional information: Invalid index 0 for this OleDbParameterCollection with Count=0."

 

Also, what do I do with DataAdapter line (Italics) if I no longer have the sql string.

 

I got the first question. Adding a '?' in a query doesn't directly provide you a new Parameter. You have to explicitly add a new Parameter before you can assign a value.

 

About the second question. I don't get where you're going with your question.

[TedN]

I got the first question. Adding a '?' in a query doesn't directly provide you a new Parameter. You have to explicitly add a new Parameter before you can assign a value.

 

About the second question. I don't get where you're going with your question.

 

Thanks for your reply Amir.

 

I was earlier pointed to a couple of links which showed the use of parameters. I then tried to use this info to run my small test program. That's when I got stuck.

 

Maybe the best way to explain it is to show you my test program using a sql string and then show the example I tried to utilize at one of the links.

 

Test Program

   Private Sub btnLoad_Click(ByVal sender As System.Object, _
   ByVal e As System.EventArgs) Handles btnLoad.Click

       Dim con As New OleDb.OleDbConnection
       Dim ds As New DataSet
       Dim da As OleDb.OleDbDataAdapter
       Dim sql As String
       Dim dv1 As New DataView


       con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
       & "Data Source = C:\AddressBook.mdb"

       sql = "SELECT * FROM tblContacts WHERE Surname = 'Smith'"

       con.Open()
       da = New OleDb.OleDbDataAdapter(sql, con)
       da.Fill(ds, "Test")
       con.Close()

       dv1.Table = ds.Tables("Test")
       Me.DataGrid1.DataSource = dv1

   End Sub
End Class

 

 

 

Parameter Example (C#)

set your sql string = to something like

 

cmd.CommandText = "SELECT * FROM MYTABLE WHERE NAME = ? and DOB > ?"

 

then add parameters for each of your '?' like this:

 

cmd.Parameters.Add(new OleDbParameter("NAME", OleDBType.VarChar);
cmd.Parameters.Add(new OleDbParameter("DOB", OleDBType.DateTime); 

 

prepare your command. This compiles your command for faster execution. Command Preparation is implicit and does not really need to be called as it will be called internally the first time the command is executed. In some rare cases explicitly calling it can speed performance.

 

cmd.Prepare 

 

your cmd is ready to be used by just setting the parameter values:

 

cmd.Parameters["NAME"] = "Miller"; 
cmd.Parameters["DOB"] = DateTime.Parse("12/2/1959");
cmd.ExecuteReader();

 

If you could show me how my test program could be implemented using parameters I would be much obliged.

 

Thanks,

Ted

[PlausiblyDamp]

It would be something like

Private Sub btnLoad_Click(ByVal sender As System.Object, _
   ByVal e As System.EventArgs) Handles btnLoad.Click

Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter

con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
       & "Data Source = C:\AddressBook.mdb"

con.Open()
Dim cmd As New OleDb.OleDbCommand("Select * from tblContacts where Column2 = ?", con)

cmd.Parameters.Add("Surname", OleDbType.VarChar)
cmd.Parameters(0).Value = "Smith"

da = New OleDb.OleDbDataAdapter(sql, con)

da.Fill(ds, "Test")
con.Close()

txtFirstItem.Text = ds.Tables("Test").Rows(0).Item(1)
txtSecondItem.Text = ds.Tables("Test").Rows(0).Item(2)

End Sub

[TedN]

Thanks for your reply. I'm getting an error regarding:

 

da = New OleDb.OleDbDataAdapter(sql, con)

 

With the use of parameters, the variable sql isn't set to anything.

 

What would I put in DataAdapter statement.

 

Thanks,

Ted

[TedN]

OK. I finally got it to work.

 

   Private Sub Form1_Load(ByVal sender As System.Object, _
   ByVal e As System.EventArgs) Handles MyBase.Load

       Dim da As OleDb.OleDbDataAdapter
       Dim cmd As New OleDb.OleDbCommand
       Dim con As OleDb.OleDbConnection
       Dim ds As New DataSet
       Dim dv As New DataView
       Dim sql As String

       da = New OleDb.OleDbDataAdapter(cmd)

       con = New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
       & "Data Source = C:\AddressBook.mdb")

       sql = "SELECT * FROM tblContacts WHERE Surname = @sName"
       cmd.Parameters.Add("@sName", OleDb.OleDbType.Char).Value = "Smith"

       cmd.Connection = con
       cmd.CommandText = sql

       con.Open()
       da.Fill(ds, "Test")
       con.Close()

       dv.Table = ds.Tables("Test")
       Me.DataGrid1.DataSource = dv

   End Sub

 

Can't say I've got a complete handle on it yet but it's a start.