|
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 Textbox1 value is "Hello" and your DropDownList1 selected value is "World", you would want your strSQLQuerry variable 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 Textbox1 and DropDownList1 (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.Format method 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 your Textbox1. 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 Table1 gets 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 placeholders. So depending on your database, you might use a string like this:
strSQLQuerry = "Select * From Table1 Where Field1 = @param1 AND Field2 = @param2" or this
strSQLQuerry = "Select * From Table1 Where Field1 = ? AND Field2 = ?" You would create a Command object appropriate for your database, set its CommandText property to strSQLQuerry, and add Parameter objects with values to its Parameters collection. For example, assuming you use VB and SQL Server, you may have the following:
'-- setup the select command text
strSQLQuerry = "Select * From Table1 Where Field1 = @param1 AND Field2 = @param2"
'--create the SqlCommand object (a connection would need to be set up too)
dim cmd as SqlCommand = new SqlCommand(strSQLQuerry)
'--add parameter values retrieved from the submitted form
cmd.Parameters.Add("@param1", Textbox1.Text)
cmd.Parameters.Add("@param2", DropDownBox1.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)I know this seems like a lot, but parameterized queries are really the way to go. The links I gave you before should help. In the "Server-Side Data Access" quickstart is a section called "Performing a Parameterized Select" that explains this more.
Best wishes and happy new year.
|
|
|
|
|
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
Line 177: da.Fill(ds)
Thanks and have a happy new year to you too.
|
|
|
|
|
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 SqlDataReader but 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.
|
|
|
|
|
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 Text from the drop down list and not the Value. (The Text property is the one that is shown to the user, the Value is used internally)
Does this help?
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
I am a new comer in MySQL area, and I want to build an database application with Visual Studio .net 2003. My questions are:
1. How can I insert records into MySQL table in .net2003?
2. Can MySQL support image data in the record (like JPG/BMP file)?
I searched these topics in CP but didn't find answer. Can anybody suggests some articles or sample code about this?
Thanks a lot!
Kevin
|
|
|
|
|
|
Hi, how can I get working the procedure, where only last 50 rows are returned: I have found something about SELECT TOP 50 in MSDN but I can't get it working. I have this code (procedure)
CREATE PROCEDURE dbo.GetGuestbookData
(
@SortOrder varchar(64)
)
AS
IF @SortOrder='ASC'
BEGIN
SELECT *
FROM Guestbook INNER JOIN Users
ON Guestbook.AuthorID = Users.RecordID
ORDER BY Guestbook.RecordID ASC
END
ELSE
BEGIN
SELECT *
FROM Guestbook INNER JOIN Users
ON Guestbook.AuthorID = Users.RecordID
ORDER BY Guestbook.RecordID DESC
END
RETURN
|
|
|
|
|
Hi,
Just Replace ur Query with This one
SELECT TOP 50 *
FROM Guestbook INNER JOIN Users
ON Guestbook.AuthorID = Users.RecordID
ORDER BY Guestbook.RecordID ASC
Hope this will Help........................:->
Regards,
Ritesh
|
|
|
|
|
|
I am inserting string into Excel file Using ADO .Execute fn(). It copies d first 3 records frm .txt file which r of size 20-30 char but as soon d 4th record which is 150 char long with special char is encountered it gives (((error:The field is too small to accept the amount of data you attempted to add.Try inserting or pasting less data..)))
I manually changed the Format of Field frm text to General in Excel file and copy it so it worked but... how to go about programatically without changing from text to general the file format
Codes:
SQL.Format("INSERT INTO [Sheet1$] values('%s', '%s', '%s', '%s', '%s', '', '')", sJobName, sMID, sDT, sAN, sTitle);
m_pConXLS->Execute(_bstr_t(sSQL), NULL, adCmdText);
Thnx in Advance
Casper2080
|
|
|
|
|
hi,
when i put a string which is in unicode into my sqlcommand directly the command can not be executed, for example if i use this code in my application:
com.Commandtext = "select * from mytable where name='" + myString +"'";
where myString is in unicode(any coding except ASCII). after executing the command an exeption is thrown.
i want to know if there is a way rather than using sqlparameters to put a string in command?
|
|
|
|
|
|
Hello
I am unable figure out the following problem.I am using ODP.NET 9.2.0.4 and ORACLE 8i Server. While I am trying to call an Oracle function which returns a VARCHAR2 at the time of ExecuteNonQuery it generates the OracleExecption("ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1").
But No problem occurs while function returns Number.
Please help me.Thanks in advance
Arnab
Oracle Function:
<br />
<br />
FUNCTION Get_DATA1 (p_val IN VARCHAR2)<br />
RETURN VARCHAR2 IS<br />
<br />
v_val VARCHAR2(10);<br />
BEGIN<br />
v_val:=p_val;<br />
RETURN v_val;<br />
END Get_DATA1;<br />
VB.NET Function That accessing the Above function:
<br />
Public Function GetData1(ByVal strValue As String) As String<br />
<br />
Dim strConnection As String<br />
Dim objConnection As OracleConnection<br />
Dim objCommand As OracleCommand<br />
Dim objParameterReturn As New OracleParameter<br />
<br />
Dim strReturnValue As String = ""<br />
<br />
Try<br />
strConnection = "My Oracle connection String"<br />
objConnection = New OracleConnection(strConnection)<br />
objCommand = New OracleCommand("Get_DATA1", objConnection)<br />
objCommand.CommandType = CommandType.StoredProcedure<br />
objCommand.Parameters.Add("p_val", OracleDbType.Varchar2, 10, strValue, ParameterDirection.Input)<br />
'setting parameter for return value<br />
objParameterReturn = objCommand.Parameters.Add("retval", OracleDbType.Varchar2, 10, Nothing, ParameterDirection.ReturnValue)<br />
objCommand.Connection.Open()<br />
'Upto connection open is working fine<br />
'but ExecuteNonQuery casues the OracleException<br />
objCommand.ExecuteNonQuery()<br />
strReturnValue = CType(objParameterReturn.Value(), String)<br />
<br />
Catch ex As OracleException <br />
strReturnValue="Oracle Error"<br />
Catch ex As Exception<br />
strReturnValue="Other Error" <br />
Finally<br />
''Release All DB objects<br />
ReleaseDB(objCommand, objConnection)<br />
End Try<br />
<br />
Return strReturnValue<br />
End Function<br />
|
|
|
|
|
I tried to insert a record to database(in user register operation) using Access 2003 database and an exception appears("this operation must use updateable queries.").But when i use the same code with SQL2000 Database,every thing is OK.
Anyone can explain for me,and how to insert a record to Access database ? thanks.
the full code:
/////////////////////////////////////////////////
bool result = false;
//OLEDB
OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(CfgKeyOleConnString+CfgKeyOleDatabase);
string strSQL = "INSERT INTO Users (UserName,Name,UserPassword,Email,RoleID) VALUES (@UserName,@Name,@UserPassword,@Email,@RoleID)";
OleDbCommand dbComm = new OleDbCommand(strSQL,dbConnection);
dbComm.Parameters.Add(new OleDbParameter ("@UserName", OleDbType.VarChar, 50));
dbComm.Parameters["@UserName"].Value = userName;
dbComm.Parameters.Add(new OleDbParameter ("@Name", OleDbType.VarChar, 50));
dbComm.Parameters["@Name"].Value = displayName;
dbComm.Parameters.Add(new OleDbParameter ("@UserPassword", OleDbType.VarChar, 50));
dbComm.Parameters["@UserPassword"].Value = userPassword;
dbComm.Parameters.Add(new OleDbParameter ("@Email", OleDbType.VarChar, 50));
dbComm.Parameters["@Email"].Value = userEmail;
dbComm.Parameters.Add(new OleDbParameter ("@RoleID", OleDbType.Numeric , 8));
dbComm.Parameters["@RoleID"].Value = Convert.ToInt32(Role);
try
{
dbConnection.Open();
dbComm.ExecuteNonQuery();
result = true;
}
catch(Exception ex)
{
Console.WriteLine(ex.ToString());
result = false;
}
finally
{
if (dbConnection.State==ConnectionState.Open)
dbConnection.Close();
}
return result;
...NLD...
|
|
|
|
|
Hi there. Your SQL statement begins with "INSERT Into Users ..." In your Access database, is "Users" a table or a query?
|
|
|
|
|
It's a table name.
...NLD...
|
|
|
|
|
Morning all,
Was wondering if anyone can help on replication as it's kind of urgent now and the help would be so greatly appreciated if anyone can.
What i have is a MSDE installation on several 100 clients and a SQL Server 2000 installation on the server.
I have set up merge replication on the local network and it work fine.
The problem is that several clients never login to the LAN and others don't even have windows user accounts (VPN is out of the window) but they also need to have their local databases updated.
All users need to be able to work locally and those changes need to be merged back into the server database without conflicts (or handled conflicts.)
Now, with FTP Merge Replication this would be a relatively simple process. However, the powers that be have made it quite clear that they are not going to let me use FTP, which i advised makes my job a lot harder and thanked them heartily.
Is there any way anyone can think of that this can be done through any other transfer method?
I was thinking i could mimic the process that Merge Replication goes through or maybe use parts of it. Please help....... Any suggestions would be a great start.
Thank you all.
"If i was king cigarettes would be free."
|
|
|
|
|
Hello folks,
I am using the following INSERT statement to add a new record in my table. I want to be able to get the Autonumber UserID field of the newly created record but I am getting the following error: "Characters found after end of SQL statement"
INSERT <br />
INTO tblUser <br />
(LoginName, UserPassword, EmailAddress, City)<br />
Values <br />
(@User, @UsrPassword, @EmailAddress, @City);<br />
SELECT @@IDENTITY As 'Identity'
What is wrong in my statement?
How do I retrieve the Autonumber field of the newly created record?
How do I handle if the LoginName and/or EmailAddress already exists?
I want to be able save this INSERT statement as an Access Query and call it from my C# code.
I would appreciate any help.
Thanks
Mike
|
|
|
|
|
Hi Mike. I don't think you can execute both statements in a single command with Access. You should though be able to execute the Select @@Identity statement as a second command, following the insert. Here's a link to another discussion thread[^] with information you may find helpful.
|
|
|
|
|
Thanks for the link Mike, it is very helpful.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
