I am creating a simple sql statement that returns all from the table that is equal to Comp_UserID. The problem I am having is that I want to pass in the Comp_UserID as a variable. I am not sure of how sql parameters work, i have tryed searching the msdn network but I can see nothing for begineers so if anybody has any useful links that would be great. Below is the code I am using.
//This is statement works correctly
SqlDataSource1.SelectCommand =
"SELECT * FROM ApprovedComponents WHERE Comp_UserID = 61";
//Now I tryed inserting the variable like I would pass a variable to a function but it does not work,
int num1= 61;
SqlDataSource1.SelectCommand = "SELECT * FROM ApprovedComponents WHERE Comp_UserID = num1";
Can anybody suggest a solution
Parameters in a sql statement
-Roel-
appreciated.
Matt Slat
Roman H
Thats great guys thanks. So to change the datatype of my parameter I use sqlDbType.String to change it to string and set the value I just assign the variable like pComp_UserID.Value = 56;
The parameters to the Sqlcommand dbCommand are the query and the connection In my case I use the wizards in Visual Studio to gain access to a database by binding the data to a datagrid so I would pass the Data source as my dbConn Is that right
ecast
When someone adds a post to a question you asked, you can rate this post as "the answer" to your question. By rating a post as "the answer", you help others find the answer more quickly, you give the person who posted the answer credit for their contribution, and you help increase the quality of answers in the discussion group.
Richard Hathaway
Use paramitrimized queries. Then you never have to worry about format's or SQL Injection.
It's also better for the preformance, because you don't need to have to concatenate a string for example:
string query = "SELECT * FROM Table1 WHERE ID = " + txtId.Text + " AND Name = \"" + "txtName.Text + "\"";
No escape characters needed, you doesn't have to think about using a " or not etc.
Parameters are like placeholders, you use them in Stored Procedures as well.
A little example:
// Set query and parameters.
const string query = ""SELECT * FROM ApprovedComponents WHERE Comp_UserID = @Comp_UserID";
SqlParameter pComp_UserID = new SqlParameter("@Comp_UserID", SqlDbType.Int);
pComp_UserID.Value = date;
// Create command and set the SqlDataSrouce1.SelectCommand
SqlCommand dbCommand = new SqlCommand(query, dbConn);
dbCommand.Parameters.Add( pComp_UserID );
SqlDataSource1.SelectCommand = dbCommand;
RohitMehta802003
>>The parameters to the Sqlcommand dbCommand are the query and the connection In my case I use the wizards in Visual Studio to gain access to a database by binding the data to a datagrid so I would pass the Data source as my dbConn Is that right
That's correct.
- Jeroen Boiten
ST2B
This should work:
int num1= 61;
SqlDataSource1.SelectCommand = "SELECT * FROM ApprovedComponents WHERE Comp_UserID = " + num1;
- Jeroen Boiten
CarolChi
When Comp_UserID is a string field in your database, set the SqlDbType to string. You specify the type of the field IN the database.