Include SQL Code in VB.Net

[TedN]

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

Im building a sql string and the whole thing is getting rather unwieldy. So Im 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 doesnt (or you choose not to use them) then parameterised queries are probably what you are after - search these forums for Parameterised Queries and youll find several hits.

Some quick ones are
http://www.computerhelp.forum/showthread.php?t=87082 and http://www.computerhelp.forum/showthread.php?t=84159

 

[TedN]

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

Some quick ones are
http://www.computerhelp.forum/showthread.php?t=87082 and http://www.computerhelp.forum/showthread.php?t=84159

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

Im still struggling a bit though to get parameters to work and would appreciate it if you could tell me where Im 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 doesnt 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 dont get where youre going with your question.

 

[TedN]

I got the first question. Adding a ? in a query doesnt 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 dont get where youre 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. Thats 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. Im getting an error regarding:

da = New OleDb.OleDbDataAdapter(sql, con)

With the use of parameters, the variable sql isnt 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

Cant say Ive got a complete handle on it yet but its a start.