Passing parameter for Stored Procedure to Data Adapter/Data Set

dotnet framework


  • 1. Cannot open database ''.
    The full error message is: Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt. I'm seeing this error once in a while in a C# windows app that talks to a MS Access database (2000 file format). The connection string is set at startup and never changed. It is in the format: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=(path to my db);Jet OLEDB:Database Password=(the password); All commands executed against the db go through the same method, which ensures that connections are opened, used, closed and released. The app is NOT very data-intensive, so it's difficult for me to believe this is a "number of connections" issue. Anybody have an idea what this error is trying to tell me? TIA Jared
  • 2. GetOpenConnection state exceptions
    Using Windows Server 2003, SQL Server 2005, and .net 2.0 (C#) I'm intermittently getting exceptions like the following: System.Data.SqlClient.SqlConnection(GetOpenConnection)ExecuteNonQuery requires an open and available Connection. The connection's current state is connecting. System.Data.SqlClient.SqlConnection(GetOpenConnection)ExecuteNonQuery requires an open and available Connection. The connection's current state is open. System.Data.SqlClient.TdsParserStateObject(ExecuteFlush)Operation cancelled by user. These are now occurring in service processes that have been behaving properly for several months.
  • 3. ODBC - Error
    I am getting the followign error when using ODBC from ADO.NET..... System.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'StoredProcedure' expects parameter '@Parameter', which was not supplied. I installed latest MDAC , still i am getting error... any help?
  • 4. Paradox Database
    Hello, I've a paradox database file named master.db. What is the best way to access this using (VB)? I want to put the data in a gridview. - Thanks
  • 5. Trouble with DbConnection.GetSchema()
    Hello, I trying to get some schema info with help of string of code: _conn.GetSchema("Tables", sa) where _conn is type of DbConnection. So - sa must be string[] type. If I define sa like this: string[] sa = new string[3]; sa[0] = "master"; sa[1] = null; sa[2] = "users"; all fine, I get row "master dbo users BASE TABLE". This was SYSTEM database. Now want the same but for my own MSSQL database with name "TestBase". Such database definitely exist on the same server and definitely has table "MyTbl2". So I re-define sa: string[] sa = new string[3]; sa[0] = "TestBase"; sa[1] = null; sa[2] = "MyTbl2"; and get... nothing. Just empty DataTable? :( What's wrong?

Passing parameter for Stored Procedure to Data Adapter/Data Set

Postby Um90Y2l2 » Thu, 28 Sep 2006 00:01:02 GMT

Hey all,

   I'm trying to pass a parameter into my Stored procedure to be accessed by 
my data adapter into a dataset

            DataSet rsDataset = db2.Fill("spListMappingDetails", 

However, when i try to run it, it gives me an error 

{"Procedure 'spListMappingDetails' expects parameter '@intClientId', which 
was not supplied."}

Any help is appreciated.

I used a executescalar and executequery function and it has no problem. 

thanks in advance

Re: Passing parameter for Stored Procedure to Data Adapter/Data Set

Postby William (Bill)Vaughn » Thu, 28 Sep 2006 01:37:45 GMT

While your code creates the parameter, it does not set the Value property.

William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.

Re: Passing parameter for Stored Procedure to Data Adapter/Data Set

Postby piggy » Thu, 28 Sep 2006 10:40:18 GMT


I do not believe that parameter names are case insensitive. Did you try 
adding parameter as '@intClientId' instead of '@intClientID'


to be sure the cmbClientList has a selected value, try using a string 
variable to get the cmbClientList.SelectedValue.ToString and (after) use 
the string variable as argument for AddParameter:

mystring = cmbClientList.SelectedValue.ToString()

hope this will help

Similar Threads:

1.Passing parameter for Stored Procedure to Data Adapter/Data Se

2.Data Adapters, Data Sets, Data TableMappings, and XML

Here's what I'm trying to do:

1. Create a data adapter with Select and Insert commands.

2. Use DataTableMappings to map XML tags into columns in a 
data base table.

3. Create a dataset

3. Read in a schema using dataset.ReadXmLSchema

4. Read in an xml file that contains the data using 
dataset.Read XML

5. Add 3 new columns to the dataset. (These columns have 
already been mapped in step 2).

6. Put data in the 3 new columns.

7. Modify some data in the other columns using xpath 
queries to find the nodes that need changing.

8. Use datadapter.update(dataset, tablename)

Everything seems to work fine until step 8. I get all the 
data, the extra columns get added, and the data in other 
columns gets modified. When I execute step 8 I get:

Run-time exception thrown : 
System.InvalidOperationException - Cannot add or remove 
columns from the table once the DataSet is mapped to a 
loaded XML document.

Any ideas about how I can either fix this or do it another 


3.problem with data adapter and data set while inserting and retrieving data

i am using a data adapter and a dataset for filling and retrieving data
into .mdb database.
following is the code.....

for the form load event
  Dim dc(0) As DataColumn
            If OleDbConnection1.State = ConnectionState.Closed Then
                MsgBox("connection can not be established")
            End If
            DA.Fill(DataSet11, "Table1")
            cmd = New OleDbCommandBuilder(DA)
            dc(0) = DataSet11.Tables("Table1").Columns("EmpID")
            DataSet11.Tables("Table1").PrimaryKey = dc
        Catch ex As Exception
        End Try
        Delete.Enabled = False
    End Sub

for  inserting values into the DB

Private Sub Insert_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Insert.Click
        Dim dr As DataRow
        dr = DataSet11.Tables("Table1").NewRow
        dr.Item(0) = Val(TB1.Text)
        dr.Item(1) = TB2.Text
        dr.Item(2) = TB3.Text
        dr.Item(3) = Val(TB4.Text)
        dr.Item(4) = TB5.Text
        DA.Update(DataSet11, "Table1")
        MsgBox("data is saved")
        rno = 0
        call filldata()

filldata function consists of the following

 With DataSet11.Tables("Table1").Rows(rno)
         TB1.Text = Trim(.Item(0))
         TB2.Text = Trim(.Item(1))
         TB3.Text = Trim(.Item(2))
         TB5.Text = Trim(.Item(4))
         End With

the error it gives is " there is no row at
0........system.nullreference...........i checked the connection and
its working fine and also the database is getting accessed........the
error is occuring at the line " With
DataSet11.Tables("Table1").Rows(rno) "

4.Table Adapter Configuration Wizard replaces data table name with stored procedure's


When using Table Adapter Configuration Wizard if 'Use SQL Statements' is 
selected as Command Type, the data table's name in dataset is retained and 
only its data adapter's select statements are replaced. If however 'Create 
new stored procedures' is selected as Command Type, the data table name in 
replaced by the name of the newly created select stored procedure. Problem 
with this is that the data table's name needs to be put back manually.

Is there a way to keep the data table name the same and just stored 
procedures are assigned to the data table's data adapter?



5.Need practical advice on using single stored procedure to process data adapter commands

Im trying to minimize the number of stored procedures and was
wondering if there is any downside to using a single stored procedure
to handle the select,insert,update, and delete commands for a data

I create the sql dataadapter using system tables to generate the
parameters and add an additional parameter to denote the type of

Would appreciate any experienced criticisms.....thanks

6. Assign new stored procedure to data adapter

7. Customizing stored procedure generated by data adapter wizard.

8. use existing stored procedure in data adapter configuration wizard is disabled

Return to dotnet framework


Who is online

Users browsing this forum: No registered users and 95 guest