• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:


In attempt to get away from using the DataAdapter Wizard - it's causing problems with the connection the we are using.  I need to hand code in the adpaters.  I'm looking for a good example of one that is using a stored procedure that needs a parameter or multiple parameters.

Any help would be appreciated.

my connectionstring is using the app.config file (example)

        Me.SqlConnection1.ConnectionString = AppSettings.Get("ABRConn")

When using the wizard this string can not be selected and if the connection string in the adpater is changed with the wizard it removes the above code.

thanks in advance.

2 Solutions
Dim myConn As OleDbConnection = New OleDbConnection _
   ( "Provider=SQLOLEDB; Data Source=localhost; " & _
   "Integrated Security=SSPI; Initial Catalog=northwind" )
Dim salesCmd As OleDbCommand = New OleDbCommand _
   ( "SalesByCategory", myConn )
salesCmd.CommandType = CommandType.StoredProcedure

Dim myParam As OleDbParameter = salesCmd.Parameters.Add _
   ( "@CategoryName", OleDbType.NVarChar, 15 )
myParam.Value = "Beverages"

myConn.Open ( )
Dim myReader As OleDbDataReader = salesCmd.ExecuteReader ( )
Response.Write ( myReader.GetName ( 0 ) & ", " & _
   myReader.GetName ( 1 ) & "<br>" )

Do While myReader.Read ( )
   Response.Write ( myReader.GetString ( 0 ) & ", " & _
      myReader.GetDecimal ( 1 ) & "<br>" )

myReader.Close ( )
myConn.Close ( )
You may want to put your connection string into an external text file instead of into the config file. Then u retrieve it everytime when your system runs by reading that file.

next you can write out your stored procedure in SQL server. You can refer to the following link on how to pass parameter into the stored procedure you have written:

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now