I have the following code:
and I'm trying to use that to submit to my database, but when I click on the submit button, no data is being inserted into my database, but I'm also not receiving any errors. Can someone maybe point out why?
Thank you
Doug
20  1986 
You should not be creating your SQL command by concatenating user input directly into it. This leaves you wide open to a SQL Injection attack (where the user inputs SQL instead of the intended value and because you are adding it into your SQL command, their SQL command gets executed). You should be using Parameters to avoid this type of attack.
Please review: How to use a database in your program.
After modifying your code to use parameters, see if it works. You can check the number of rows that were effected by the update by checking the Integer that is returned by the ExecuteNonQuery() method.
-Frinny
Frinny,
If the best way would be to use Parameters to input data directly into my SQL with the drop down lists and text boxes I have, since I'm taking values from the users, what would be the best way to accept input from the users?
Doug
You accept the input from the users using TextBoxes and DropDownLists and any other types of controls you need to do the task.
Then you validate that the input they provided is correct according to your requirements.
Then you supply the values the input to the SqlCommand using it's Parameters Property so that the user input can be used to query or update your database.
When you use parameters, any user provided input will be treated as a "Literal" instead of as part of the command. It is not compiled into the SQL query/update that you are going to execute.
Does this make sense?
Frinny,
Ok so then because I'm using regular expression validation for most of the text boxes, what you're telling me then is that by using parameters, that instead of doing my validation via expression validation, that I'm shifting away from client validation to server side validation correct? Also if that's the case, can you give me an example of how to capture a text box's input in a sqlcom.parameters.add statement?
Thank you,
Doug
Nope,
I'm telling you to leave all of your validation in.
Do validation client-side and server side...but still participate in good database input-sanitation practices by using parameters.
You should have several layers of data validation and when it comes to entering data into a database you should still use parameters.
-Frinny
Ok so since I've never worked with parameters before, can you give me an example so I won't have to come back later and ask.
Thank you
Doug
Frinny,
What I don't know is how to pass the parameters from a text box to the add.parameters. That's my question.
I can understand the structure but don't understand where the textbox or dropdown values would be in the statements.
I see.
Like this: -
Dim connectionString As String = "Data Source=10.2.1.41;Initial Catalog=MDR;uid=xxxxx;password=xxxxxxx"
-
-
Dim commandText As String = _
-
"insert into Exceptions2 " & _
-
"values(@exceptiondate, @starttime, @address, @endtime, @duration)"
-
-
-
Using connection As New SqlConnection(connectionString)
-
Dim command As New SqlCommand(commandText, connection)
-
command.Parameters.Add("@exceptiondate", SqlDbType.VarChar)
-
command.Parameters("@exceptiondate").Value = exceptiondateInput.Text
-
-
command.Parameters.Add("@starttime", SqlDbType.VarChar)
-
command.Parameters("@starttime").Value = starttimeInput.Text
-
-
command.Parameters.Add("@address", SqlDbType.VarChar)
-
command.Parameters("@address").Value = txtAddress.Text
-
-
command.Parameters.Add("@endtime", SqlDbType.VarChar)
-
command.Parameters("@endtime").Value = endtimeInput.Text
-
-
-
command.Parameters.Add("@duration", SqlDbType.VarChar)
-
command.Parameters("@duration").Value = duration.Text
-
-
Try
-
connection.Open()
-
Dim rowsAffected As Integer = command.ExecuteNonQuery()
-
-
Catch ex As Exception
-
myErrorMessageLabel.Text = ex.Message
-
End Try
-
End Using
-Frinny
Frinny thanks. That helps a lot. One last question, what's the best way to pass my dropdown list variables to the sql server? This one is databound -
<asp:DropDownList ID="OperatorDropdown" runat="server"
-
DataSourceID="SqlDataSource1" DataTextField="employeenumber"
-
DataValueField="employeenumber" Height="23px" Width="130px">
-
</asp:DropDownList>
-
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
-
ConnectionString="<%$ ConnectionStrings:MDRConnectionString %>"
-
SelectCommand="SELECT [employeenumber] FROM [Employees]">
-
</asp:SqlDataSource>
-
While the other two are not.
I haven't worked with bound DropDownLists in the past but I would think that you could just access the OperatorDropdown.SelectedItem.Value property and set the parameter's value to it... like I did above.
so like this for the databound dropdown? -
command.Parameters.Add("@employeenumber", SqlDbType.Varchar)
-
command.Parameters("@employeenumber".value = OperatorDropdown.SelectedItem.Value
-
and like this for the non-databound? -
command.Parameters.Add("@code", SqlDbType.Varchar)
-
command.Parameters("@code").Value = listitem.value
-
You're missing a")" in your code. It should be.... -
command.Parameters.Add("@employeenumber", SqlDbType.Varchar)
-
command.Parameters("@employeenumber").value = OperatorDropdown.SelectedItem.Value
When you bind your DropDownList you set the DataTextField and the DataValueField properties. These are not necessarily the same thing (in your case they are set to the same thing). You would need to choose the correct "SelectedItem" property accordingly. In your case either OperatorDropdown.SelectedItem.Value or OperatorDropdown.SelectedItem.Text will work.
Frinny,
What about for the non-bound dropdown? Is that correct? Oh and thank you for pointing out the missing ")". I corrected that.
Same thing for a non-bound DropDownList :)
You add ListItems to a DropDownList and they work the same way because ListItems have Text and Values.
-Frinny
Frinny,
Is there a way that I can insert a datediff sql statement here to determine the difference between the starttimeInput and the endtimeInput boxes here?
Sure.
Retrieve the text from the TextBoxes and create 2 DateTime variables (one for the start time and one for the end time).
Use the DateTime.TryParse() method populate the DateTime variables.
Then subtract the Start time from the End Time to get the "delta time" (the change in time).
Like this: - Dim startTime As DateTime
-
DateTime.TryParse("9:00", startTime)
-
'you could also use: DateTime.TryParse("9:00 AM", startTime)
-
-
Dim endTime As DateTime
-
DateTime.TryParse("17:00", endTime)
-
'you could also use: DateTime.TryParse("5:00 PM", endTime)
-
-
Dim timeInterval As TimeSpan = endTime - startTime
-
-
Dim deltaHours As Integer = timeInterval.Hours
Once calculated, you can store this information in your database.
Frinny,
So where you have this
DateTime.TryParse ("9:00", startime)
Is that passing the variable for the textbox into this?
That will Try to convert the string "9:00" into a DateTime variable.
The TryParse method will return false if it can't parse the string into a DateTime type.
This is yet another layer of data validation that you can add to your application.
So what you want to do is pass the time that the user entered into the TryParse method instead of "9:00" (a hard coded string).
So then would I put the value of starttime.text there since that's where I'll be getting the data from, instead of "9:00?"
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Arda |
last post by:
Hi all,
I tried to connect a database(SQL) by both VS 2003 interface tools and
normal typing.
When I make the connection in local Web project it doesn't work and gives an
error like this;
...
|
by: Makarand Keer |
last post by:
Hi all Gurus
I have asp.net application where I am creating background process using
Threading.
My threading involves invoking method which in turn gets some data from SQL
server using Windows...
|
by: farhan |
last post by:
Please let me know code used to simply connect a database
and then add, delete, update records in asp.net using
vb.net.
|
by: spiewak |
last post by:
Is this possible to connect do the Database Engine (on sql server 2005 on XP
platorm - file *.mdf (not mobile *.sdf)) form win CE 5.0 ?
I tried to do this
ConnectionStringSQLServerCE = "Data...
|
by: spiewak |
last post by:
Is this possible to connect do the Database Engine (on sql server 2005 on XP
platorm - file *.mdf (not mobile *.sdf)) from win CE 5.0 ?
I tried to do this :
ConnectionStringSQLServerCE = "Data...
|
by: hussainiyad |
last post by:
hi to all this is hussain i need ur help.
in a datagrid there r two textboxes and a button and a label, if i type any value in that textboxes and click the button if the values r equal then the msg...
|
by: xtianixm |
last post by:
How To Connect Mysql Database Through Network!
|
by: sravani1 |
last post by:
I am new to the php. i wrote the code to connect to the database. I will use the following code.
<?php
$con = mysql_connect("localhost","root","root");
if (!$con)
{
die('Could not connect:...
|
by: debasisdas |
last post by:
This is a sample code for taking backup and restore of access database
Dim DBTempSource As Database
Dim DBTempDestination As Database
Dim RecTempSource As Recordset
Dim RecTempDestination...
|
by: ezrockgw |
last post by:
i want to make an application in j2me so that it interacts with database and keeps updating it.
How can i connect my mobile to the pc which has the database?...Will it be through bluetooth which i...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
