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 can't 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 can't 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=cache:ooI407s2YQsJ:www.dbazine.com/sql/sql-articles/cook6+executing+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 don't have to call varprojsqlconn.open and you don't have to specify the connection for the oledbDataAdapter either because you've 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!