Hello to all.. When my web form runs it asks for project code from the anonymous user, on entering that code the value is passed via session variable to a local variable which then checks that value with the records in the database,the local variable is passed in an adhoc query. After making a connection with Sql server. Now, i want to pass the same value in a stored procedure. I know
for that i have to take a @variable but how should i pass the value of
the mcode variable in that stored procedure.
Dim myQry1 As String = "exec gridreport"
Dim MCode As String
If Session("UserLogedIn") = "Yes" Then
MCode = Session("ProjectCode")
End If
If e.Item.ItemType = ListItemType.Footer Then
Dim myCon1 As New SqlConnection("data source=.;user id=sa;password=;initial catalog=hb")
myCon1.Open()
Dim myQry1 As String = "select sum(b.Onsite) as onsite,sum(b.Offshore) as offshore,sum(b.Offon) as offon,sum(b.Onsite+b.Offshore+b.Offon) as Total,sum(b.Revenue) as revenue from Projmaster a,Projslave b where a.P_Id=b.P_Id and a.M_Code='" & MCode & "'"

'Dim myQry1 As String = "exec gridreport" (how should i pass MCode value to this stored procedure)

Dim myDa1 As New SqlDataAdapter(myQry1, myCon1)
Dim myDS1 As New DataSet
dg_summary.DataSource = myDS1.Tables("adc")
Dim myCmd As New SqlCommand(myQry1, myCon1)
Dim myDR As SqlDataReader = myCmd.ExecuteReader
If myDR.Read Then
e.Item.Cells(3).Text = myDR.GetInt32(0)
e.Item.Cells(4).Text = myDR.GetInt32(1)
e.Item.Cells(5).Text = myDR.GetInt32(2)
e.Item.Cells(6).Text = myDR.GetInt32(3)
e.Item.Cells(7).Text = myDR.GetInt64(4)
'e.Item.Cells(6).Text = myDR.GetInt32(4)
'e.Item.Cells(7).Text = myDR.GetInt64(5)
End If
End If

You need to create a new instance of the SqlParameter class and add that to the SqlCommand.Parameters collection. Also, it isn't necessary to include "exec " in the command text if you specify the command as a stored procedure.

myCmd.CommandText = "gridreport"
myCmd.CommandType = CommandType.StoredProcedure
myCmd.Parameters.Add(new SqlParameter("@variable", MCode))

Then execute the command.

-Peter

Ya, that can be done but i am using myQry1 as string in which i am executing stored procedure and then passing this variable as the first parameter of SqlDataAdapter and then in SqlCommand. For using

1.myCmd.CommandText = "gridreport"
2.myCmd.CommandType = CommandType.StoredProcedure
3.myCmd.Parameters.Add(New SqlParameter("@pcode", MCode))

what first parameter should i pass in SqlDataAdapter and SqlCommand.

4.Dim myDa1 As New SqlDataAdapter(myQry1, myCon1)
5.Dim myCmd As New SqlCommand(myQry1, myCon1)

myQry1 is a string type variable which is executing stored procedure.
If i use step (1-3) what parameter should i pass in step 4-5.

Ya, I figured out that parameter problem. HOw can use the same concept in case of dataadapters.

You are creating a SqlCommand object.

In your current example, you get a reader by calling SqlCommand.ExecuteReader.

For use in a DataAdapter, you simply provide the configured SqlCommand instance to the adapter when you create it:

Dim objAdapter As New SqlDataAdapter(myQry1)

This will assign myQry1 as the value for the SqlDataAdapter.SelectCommand property. The same would be accomplished this way:

Dim objAdapter As New SqlDataAdapter()
objAdapter.SelectCommand = myQry1

When you use the .Fill() method of the data adapter, it uses the provided SelectCommand to retrieve the data.

-Peter

Thanks.