SQL Statement Problem
-
Hi, I don't know much SQL & I need some help with my SQL statement. Hopefully someone will be able to help me. I'm triny to retrieve data using a textbox and dropdownlist. How should I write a SQL statement for this. Thanx in advance.
It depends on what you are trying to do. Presumably you need to generate a SQL select statement and execute that against the database. Are you using the textbox and dropdown to specify the data you want to retrieve from the database, i.e. do you want the contents of these to be used within SQL clauses?
-
It depends on what you are trying to do. Presumably you need to generate a SQL select statement and execute that against the database. Are you using the textbox and dropdown to specify the data you want to retrieve from the database, i.e. do you want the contents of these to be used within SQL clauses?
Hi Davey, Yes you are right I trying to use the contents of the text box and dropdown but I don't know how to do that. Acutually user is going to write in the textbox and then select the selection criteria from the dropdownlist which is a column in the table. This is what I have so far. strSQLQuerry = "Select * From Table1 Where Textbox1.Text.ToString() AND DropDownList1.SelectedItem.Value" As you can see its not complete, but frankly I'm having hard time visualizing it in terms of how to setup an equation. Thanx
-
Hi there. You may get a better response if you post your SQL statement. If you don't know SQL, I highly recommend starting with the W3Schools SQL tutorial[^]. Once you have a good understanding of how to construct a SQL SELECT statement (the tutorial will give you some practice at it) then take a look at the ASP.NET QuickStart tutorials[^]. In particular, the QuickStart tutorial for Server-Side Data Access[^] will connect your knowledge of constructing SQL statements to the .NET code necessary to perform such operations in an ASP.NET environment.
Hi, Mike. Thanx for your quick response. I will definitely check out the tutorials you mentioned. This is what I have so far. strSQLQuerry = "Select * From Table1 Where Textbox1.Text.ToString() AND DropDownList1.SelectedItem.Value" my question is that should I set the value of the textbox and dropdownlist to what? Thanx for ur help
-
Hi, Mike. Thanx for your quick response. I will definitely check out the tutorials you mentioned. This is what I have so far. strSQLQuerry = "Select * From Table1 Where Textbox1.Text.ToString() AND DropDownList1.SelectedItem.Value" my question is that should I set the value of the textbox and dropdownlist to what? Thanx for ur help
Hi there. If you are creating a literal SQL statement in code, one thing to remember is that you will be concatenating values into the string, such that the SQL is valid when sent to the database. For example, if your
Textbox1value is "Hello" and yourDropDownList1selected value is "World", you would want yourstrSQLQuerryvariable to contain the literal value:Select * from Table1 Where Field1 = 'Hello' AND Field2 = 'World'
(assuming Field1 and Field2 are actual fields in Table1). To get such a literal string, you would be concatenating the retrieved values from
Textbox1andDropDownList1(using the concatenation operator+for C#, or&for VB). For example, in VB this could look something like this:strSQLQuerry = "Select * From Table1 Where Field1='" & Textbox1.Text _
& "' AND Field2='" & DropDownList1.SelectedItem.Value & "'"Or, even better, you could use the
String.Formatmethod like this:strFormatString = "Select * From Table1 Where Field1='{0}' AND Field2='{1}'"
strSQLQuerry = String.Format(strFormatString, Textbox1.Text, DropDownList1.SelectedItem.Value)I hope these examples help with the conceptualization of building a literal SQL string using form input values. You should be aware though that building these kinds of literal SQL statements (where criteria from a submitted form is embedded directly in the SQL string sent to the database server) leaves you vulnerable to SQL injection attacks. This is where a malevolent user would enter something in an input box on the form specifically designed to screw with your data. One could, for example, enter something like "
'; DELETE FROM Table1; --" in yourTextbox1. The literal SQL string your code constructs then would look something like this:Select * From Table1 Where Field1=''; DELETE FROM Table1; --' AND Field2=''
Depending on the database permissions available to the user under which ASP.NET functions, such a statement could mean all your data in
Table1gets deleted. A better way overall is to use Parameter objects with your SQL statement. The SQL statement you construct in code would use literal parameter placeholders for criteria rather than literal values. These placeholders are database specific. For example, in SQL Server, these are names beginning with the @ symbol; if using the ODBC client, you would use a question mark ? for parameter pla -
Hi there. If you are creating a literal SQL statement in code, one thing to remember is that you will be concatenating values into the string, such that the SQL is valid when sent to the database. For example, if your
Textbox1value is "Hello" and yourDropDownList1selected value is "World", you would want yourstrSQLQuerryvariable to contain the literal value:Select * from Table1 Where Field1 = 'Hello' AND Field2 = 'World'
(assuming Field1 and Field2 are actual fields in Table1). To get such a literal string, you would be concatenating the retrieved values from
Textbox1andDropDownList1(using the concatenation operator+for C#, or&for VB). For example, in VB this could look something like this:strSQLQuerry = "Select * From Table1 Where Field1='" & Textbox1.Text _
& "' AND Field2='" & DropDownList1.SelectedItem.Value & "'"Or, even better, you could use the
String.Formatmethod like this:strFormatString = "Select * From Table1 Where Field1='{0}' AND Field2='{1}'"
strSQLQuerry = String.Format(strFormatString, Textbox1.Text, DropDownList1.SelectedItem.Value)I hope these examples help with the conceptualization of building a literal SQL string using form input values. You should be aware though that building these kinds of literal SQL statements (where criteria from a submitted form is embedded directly in the SQL string sent to the database server) leaves you vulnerable to SQL injection attacks. This is where a malevolent user would enter something in an input box on the form specifically designed to screw with your data. One could, for example, enter something like "
'; DELETE FROM Table1; --" in yourTextbox1. The literal SQL string your code constructs then would look something like this:Select * From Table1 Where Field1=''; DELETE FROM Table1; --' AND Field2=''
Depending on the database permissions available to the user under which ASP.NET functions, such a statement could mean all your data in
Table1gets deleted. A better way overall is to use Parameter objects with your SQL statement. The SQL statement you construct in code would use literal parameter placeholders for criteria rather than literal values. These placeholders are database specific. For example, in SQL Server, these are names beginning with the @ symbol; if using the ODBC client, you would use a question mark ? for parameter plaThanks Mike for your help, you have been a great help. This is my first experience (posting question to online forums) & it has very good. I agree with you about using parameter value instead of using as a string literal into the sql statement. I'm using SQL Server & VB.NET for this project. Okay now when I used the parameter code I got a different error which is following: Code ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Private Sub btnSrch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSrch.Click Dim objConnection As SqlConnection Dim objCommand As SqlCommand Dim objAdapter As SqlDataAdapter Dim objDataReader As SqlDataReader Dim objDataSet As DataSet Dim strSearch As String Dim StrSQLQuery As String 'Get Search strSearch = txtSrch.Text 'If there's nothing to search for then don't search ' o/w build our SQL Query execute it. If Len(Trim(strSearch)) > 0 Then 'Set up our connection. objConnection = New SqlConnection("server=(local);Integrated Security=SSPI;database=Equipment Log") '-- setup the select command StrSQLQuery = "Select * From Equip Where txtSrch = @param1 AND ddlSrchby = @param2" '--create the SqlCommand object (a connection would need to be set up too) Dim cmd As SqlCommand = New SqlCommand(StrSQLQuery) '--add parameter values retrieved from the submitted form cmd.Parameters.Add("@param1", txtSrch.Text) cmd.Parameters.Add("@param2", ddlSrchby.SelectedItem.Value) '--execute the query and fill a dataset with the results Dim da As SqlDataAdapter = New SqlDataAdapter(cmd) Dim ds As DataSet = New DataSet da.Fill(ds) 'Create new command object passing it our SQL Query 'and telling it which connection to use. objCommand = New SqlCommand(strSearch, objConnection) objConnection.Open() Else txtSrch.Text = "Enter Serach Here" End If ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Here is the error I got Fill: SelectCommand.Connection property has not been initialized. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: Fill: SelectCommand.Connection property has not been initialized. Source Error: Line 175: Dim da As SqlDataAdapter = New SqlDataAdapter(cmd) Line 176: Dim ds As DataSet = New DataSet
-
Thanks Mike for your help, you have been a great help. This is my first experience (posting question to online forums) & it has very good. I agree with you about using parameter value instead of using as a string literal into the sql statement. I'm using SQL Server & VB.NET for this project. Okay now when I used the parameter code I got a different error which is following: Code ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Private Sub btnSrch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSrch.Click Dim objConnection As SqlConnection Dim objCommand As SqlCommand Dim objAdapter As SqlDataAdapter Dim objDataReader As SqlDataReader Dim objDataSet As DataSet Dim strSearch As String Dim StrSQLQuery As String 'Get Search strSearch = txtSrch.Text 'If there's nothing to search for then don't search ' o/w build our SQL Query execute it. If Len(Trim(strSearch)) > 0 Then 'Set up our connection. objConnection = New SqlConnection("server=(local);Integrated Security=SSPI;database=Equipment Log") '-- setup the select command StrSQLQuery = "Select * From Equip Where txtSrch = @param1 AND ddlSrchby = @param2" '--create the SqlCommand object (a connection would need to be set up too) Dim cmd As SqlCommand = New SqlCommand(StrSQLQuery) '--add parameter values retrieved from the submitted form cmd.Parameters.Add("@param1", txtSrch.Text) cmd.Parameters.Add("@param2", ddlSrchby.SelectedItem.Value) '--execute the query and fill a dataset with the results Dim da As SqlDataAdapter = New SqlDataAdapter(cmd) Dim ds As DataSet = New DataSet da.Fill(ds) 'Create new command object passing it our SQL Query 'and telling it which connection to use. objCommand = New SqlCommand(strSearch, objConnection) objConnection.Open() Else txtSrch.Text = "Enter Serach Here" End If ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Here is the error I got Fill: SelectCommand.Connection property has not been initialized. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: Fill: SelectCommand.Connection property has not been initialized. Source Error: Line 175: Dim da As SqlDataAdapter = New SqlDataAdapter(cmd) Line 176: Dim ds As DataSet = New DataSet
You need to tell your command object about your connection, otherwise it doesn't know where (i.e. which SQL Server and database) to run your command. The documentation for the SqlCommand[^] object states that there is another constructor that takes the SqlConnection object as well as the command text. So, this line:
'--create the SqlCommand object (a connection would need to be set up too)
Dim cmd As SqlCommand = New SqlCommand(StrSQLQuery)should be changed to
'--create the SqlCommand object (a connection would need to be set up too)
Dim cmd As SqlCommand = New SqlCommand(StrSQLQuery, objConnection)Also, you have a line that reads
Dim objDataReader As SqlDataReaderbut since you are using a data adapter to get the data out of the database you do not need this and you may remove it. I hope this helps.
Do you want to know more? WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
-
Hi there. If you are creating a literal SQL statement in code, one thing to remember is that you will be concatenating values into the string, such that the SQL is valid when sent to the database. For example, if your
Textbox1value is "Hello" and yourDropDownList1selected value is "World", you would want yourstrSQLQuerryvariable to contain the literal value:Select * from Table1 Where Field1 = 'Hello' AND Field2 = 'World'
(assuming Field1 and Field2 are actual fields in Table1). To get such a literal string, you would be concatenating the retrieved values from
Textbox1andDropDownList1(using the concatenation operator+for C#, or&for VB). For example, in VB this could look something like this:strSQLQuerry = "Select * From Table1 Where Field1='" & Textbox1.Text _
& "' AND Field2='" & DropDownList1.SelectedItem.Value & "'"Or, even better, you could use the
String.Formatmethod like this:strFormatString = "Select * From Table1 Where Field1='{0}' AND Field2='{1}'"
strSQLQuerry = String.Format(strFormatString, Textbox1.Text, DropDownList1.SelectedItem.Value)I hope these examples help with the conceptualization of building a literal SQL string using form input values. You should be aware though that building these kinds of literal SQL statements (where criteria from a submitted form is embedded directly in the SQL string sent to the database server) leaves you vulnerable to SQL injection attacks. This is where a malevolent user would enter something in an input box on the form specifically designed to screw with your data. One could, for example, enter something like "
'; DELETE FROM Table1; --" in yourTextbox1. The literal SQL string your code constructs then would look something like this:Select * From Table1 Where Field1=''; DELETE FROM Table1; --' AND Field2=''
Depending on the database permissions available to the user under which ASP.NET functions, such a statement could mean all your data in
Table1gets deleted. A better way overall is to use Parameter objects with your SQL statement. The SQL statement you construct in code would use literal parameter placeholders for criteria rather than literal values. These placeholders are database specific. For example, in SQL Server, these are names beginning with the @ symbol; if using the ODBC client, you would use a question mark ? for parameter pla -
You need to tell your command object about your connection, otherwise it doesn't know where (i.e. which SQL Server and database) to run your command. The documentation for the SqlCommand[^] object states that there is another constructor that takes the SqlConnection object as well as the command text. So, this line:
'--create the SqlCommand object (a connection would need to be set up too)
Dim cmd As SqlCommand = New SqlCommand(StrSQLQuery)should be changed to
'--create the SqlCommand object (a connection would need to be set up too)
Dim cmd As SqlCommand = New SqlCommand(StrSQLQuery, objConnection)Also, you have a line that reads
Dim objDataReader As SqlDataReaderbut since you are using a data adapter to get the data out of the database you do not need this and you may remove it. I hope this helps.
Do you want to know more? WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
Thanx Colin for your suggestion. I've change the syntax. After that I'm not getting any syntax errors, however when run it I don't see any output either. Any idea what is wrong with our logic or maybe any property's setting need any changing. Thanx for your help.
-
Thanx Colin for your suggestion. I've change the syntax. After that I'm not getting any syntax errors, however when run it I don't see any output either. Any idea what is wrong with our logic or maybe any property's setting need any changing. Thanx for your help.
Anonymous wrote: After that I'm not getting any syntax errors The error you posted before was a runtime error. The program would not even be able to start running if there were syntax errors. A syntax error is something that stops the compiler from understanding what you wrote. Think of syntax as being the grammar of the language. Anonymous wrote: however when run it I don't see any output either Perhaps you need the
Textfrom the drop down list and not theValue. (TheTextproperty is the one that is shown to the user, theValueis used internally) Does this help?
Do you want to know more? WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
-
Hi, I don't know much SQL & I need some help with my SQL statement. Hopefully someone will be able to help me. I'm triny to retrieve data using a textbox and dropdownlist. How should I write a SQL statement for this. Thanx in advance.
Lets say ur textbox name is tt1 and ur dropdownlist control is ddl1. If you have a sql insert then while building the string dim string1 as String= "INSERT INTO TABLE1 VALUES('"&tt1.text&"','"&ddl1.selectedIndex.value&"')" Now simply execute this statement using the connection object. conn.execute(string1) Vivek Sharma