OleDbDataAdapter with stored procedures..

[lidds]

Could someone please point me in the right direction. what I want to be able to do is have an OleDbDataAdapter filled by using a stored procedure. I have used stored procedures with DataReaders:

varProjSQLConn is my connection string

        Dim myCmd As New OleDb.OleDbCommand("spQryDisp")
        myCmd.Connection = varProjSQLConn
        myCmd.CommandType = CommandType.StoredProcedure

        Dim myReader As OleDb.OleDbDataReader
        varProjSQLConn.Open()
        myReader = myCmd.ExecuteReader()
        varProjSQLConn.Close()

But when trying to use it with a DataAdapter I cant seem to figure out the correct code, I need some help on this one.

Thanks in advance

Simon

 

[trend]

Could someone please point me in the right direction. what I want to be able to do is have an OleDbDataAdapter filled by using a stored procedure. I have used stored procedures with DataReaders:

varProjSQLConn is my connection string

        Dim myCmd As New OleDb.OleDbCommand("spQryDisp")
        myCmd.Connection = varProjSQLConn
        myCmd.CommandType = CommandType.StoredProcedure

        Dim myReader As OleDb.OleDbDataReader
        varProjSQLConn.Open()
        myReader = myCmd.ExecuteReader()
        varProjSQLConn.Close()

But when trying to use it with a DataAdapter I cant seem to figure out the correct code, I need some help on this one.

Thanks in advance

Simon

something like:

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim MyConnection As New OleDbConnection("Provider=SQLOLEDB.1;User Id=" & TextBox1.Text & ";Password=" & TextBox2.Text & ";Initial Catalog=WeGIR Member Data;Data Source=secure.server.com;Use Encryption for Data=True")
        Dim ds As DataSet
        Dim da As OleDbDataAdapter

        Try

            MyConnection.Open()
            TextBox3.Text = "database open"



            Dim ValidateLogin As String = "EXECUTE sp_ValidateLogin"
            Dim Results As New OleDbDataAdapter(ValidateLogin, MyConnection)
            ds = New DataSet
            da.Fill(ds, "Results")



            MyConnection.Close()

            TextBox3.Text = TextBox3.Text & "database closed"

        Catch myerror As MySqlException
            TextBox3.Text = "error"

        Finally
            If MyConnection.State <> ConnectionState.Closed Then MyConnection.Close()
        End Try

    End Sub
End Class

Or
http://64.233.179.104/search?q=cach...g+stored+procedure+vb.net+oledb&hl=en&start=1

 

[trend]

Or did you want something like:

        Dim MyConnection As New OleDbConnection("Provider=SQLOLEDB.1;User Id=" & TextBox1.Text & ";Password=" & TextBox2.Text & ";Initial Catalog=WeGIR Member Data;Data Source=secure.server.com;Use Encryption for Data=True")
        Dim ds As DataSet
        Dim da As OleDbDataAdapter
        Dim myCmd As New OleDb.OleDbCommand("sp_ValidateLogin")
        Dim myReader As OleDb.OleDbDataReader

        myCmd.Connection = MyConnection
        myCmd.CommandType = CommandType.StoredProcedure

        Try
            MyConnection.Open()
            TextBox3.Text = "database open"

            Dim ValidateLogin As String = "EXECUTE sp_ValidateLogin"
            Dim Results As New OleDbDataAdapter(ValidateLogin, MyConnection)
            ds = New DataSet
            da.Fill(ds, "Results")

            myReader = myCmd.ExecuteReader()
            Do While myReader.Read
                TextBox4.Text = myReader.Read
            Loop
            myReader.Close()



            MyConnection.Close()

            TextBox3.Text = TextBox3.Text & vbNewLine & "database closed"

        Catch myerror As MySqlException
            TextBox3.Text = "error"

        Finally
            If MyConnection.State <> ConnectionState.Closed Then MyConnection.Close()
        End Try

 

[Puiu]

Could someone please point me in the right direction. what I want to be able to do is have an OleDbDataAdapter filled by using a stored procedure. I have used stored procedures with DataReaders:

varProjSQLConn is my connection string

        Dim myCmd As New OleDb.OleDbCommand("spQryDisp")
        myCmd.Connection = varProjSQLConn
        myCmd.CommandType = CommandType.StoredProcedure

After this I suggest you do something like this:
dim Da as new oledbDataAdapter(myCmd)
dim Ds as new Dataset

Da.Fill(Ds)

If u use a dataAdapter you dont have to call varprojsqlconn.open and you dont have to specify the connection for the oledbDataAdapter either because youve specified it for myCmd

After you fill the dataset you can bind a datagrid (for example) to that Ds

Cheers!

 

[trend]

Thanks, got it working!