ASP.net Code question (1 Viewer)

rkrause · Feb 16, 2012

Im doing a simple form of 21 fields that rights to a sql table.
my issue is i cant get it to work unless i have all variables as strings and strings fields in my tables.

here is my code and if i try to put in a field as integer or date it errors out. Im using VS 2008

my variables

Dim strinjnumber As String
Dim strEmpID As String
Dim dtInjDate As String 'dates
Dim dtInjTime As String 'dates
Dim strOsha As String
Dim strinjLoc As String
Dim strspecloc As String
Dim strbodypart As String
Dim strinjnature As String
Dim strrootcause As String
Dim strinjcause As String
Dim strsafeprov As String
Dim strsafeused As String
Dim strtreatment As String
Dim strmissed As String
Dim strrestricted As String
Dim strwhathappend As String
Dim strcorrective As String
Dim strdeptid As String
Dim strshift As String
Dim strhoursintoshift As String

my code:

Dim sqlcon As New SqlConnection("server=tpmpi01;database=vq-dsi;uid=bk;password=valley")
'checking if connection is open. if open close it and open again.
If sqlcon.State = ConnectionState.Open Then
sqlcon.Close()
End If
sqlcon.Open()
Dim strcommand As String
'the insert command is stored in strcommand
'Table Name : Contact
'Column of table : Name, Place

strcommand = "Insert into Injury(injnmbr, empID, injdate, injtime,osha, injLoc, specloc, bodypart, injnature,rootcause, injcause, safeguardsprovided, safeguardsused, treatment, daysmissed, daysrestricted, whathappend, correctiveaction, deptid, shift, hoursintoshift) values ('" + strinjnumber + "','" + strEmpID + "','" + dtInjDate + "','" + dtInjTime + "','" + strOsha + "','" + strinjLoc + "','" + strspecloc + "','" + strbodypart + "','" + strinjnature + " ','" + strrootcause + "','" + strinjcause + "','" + strsafeprov + "','" + strsafeused + "','" + strtreatment + "','" + strmissed + "','" + strrestricted + "','" + strwhathappend + "','" + strcorrective + "','" + strdeptid + "','" + strshift + "','" + strhoursintoshift + "')"

MSAccessRookie · Feb 16, 2012

The way that you have chosen to implement this should work just fine. What you are doing is building a string to hold the final INSERT Query, and because of this, all of the data is required to be in String format. You can use the Built-In Access String Conversion Functions to change any data to type String if you need to, and if you follow the rules below, the Query should work properly.

All VBA variables will be String format.
Access Data that is type String should be surrounded by Single Quote marks "'" in the Query.
Access Data that is type Date should be surrounded by Hash marks "#" in the Query.
Access Data that is type Integer or other number does not need to be marked in any way.

Give these rules a try and get back to us.

rkrause · Feb 17, 2012

Im not sure but im using Visual Studio to do my ASP, so im not sure what you mean by built in access functions? please advise?

MSAccessRookie · Feb 17, 2012

rkrause said:
Im not sure but im using Visual Studio to do my ASP, so im not sure what you mean by built in access functions? please advise?

ASP.Net has the ability to use the same Built-In Functions as Access. Do an internet search on cStr(), cDate(), and cInt(). Also, I believe that the rules regarding the Data Types will also apply.

rkrause · Feb 21, 2012

heres what i have tried and it errors out, im thinking my syntax is off...any help? I dummyed down the statement to a couple fields so that i can test.

strcommand = "Insert into Injury(injnmbr, empID, injdate, injtime,osha) values intinjNumber + ( '" + strEmpID + "','" + dtInjDate + "','" + dtInjTime + "','" + strOsha + "')"

MSAccessRookie · Feb 21, 2012

rkrause said:
heres what i have tried and it errors out, im thinking my syntax is off...any help? I dummyed down the statement to a couple fields so that i can test.

strcommand = "Insert into Injury(injnmbr, empID, injdate, injtime,osha) values intinjNumber + ( '" + strEmpID + "','" + dtInjDate + "','" + dtInjTime + "','" + strOsha + "')"

Try something more like the following:

strcommand = "Insert into Injury(injnmbr, empID, injdate, injtime,osha) values ( '" + intinjNumber + ",'" + strEmpID + "','" + dtInjDate + "','" + dtInjTime + "','" + strOsha + "' )"

rkrause · Feb 21, 2012

this is what i get when i do that, i had tried that previously...

strcommand = "Insert into Injury(injnmbr, empID, injdate, injtime,osha) values strcommand = "Insert into Injury(injnmbr, empID, injdate, injtime,osha) values ( '" + intinjNumber + ",'" + strEmpID + "','" + dtInjDate + "','" + dtInjTime + "','" + strOsha + "' )"

MarkK · Feb 21, 2012

Consider using SqlParameter objects as they appear in the Parameters collection of the SqlCommand object. In this case the data types are all handled by the system, and it is a much more readable process in your code...

PHP:

Imports System.Data
Imports System.Data.SqlClient

Public Class Class1

	Dim strinjnumber As String
	Dim strEmpID As String
	Dim dtInjDate As Date
	Dim dtInjTime As Date

	Sub Test1234()
		Dim sqlcon As New SqlConnection("server=tpmpi01;database=vq-dsi;uid=bk;password=valley")
		Dim cmd As New SqlCommand( _
		 "INSERT INTO Injury " & _
		  "(injnmbr, empID, injdate, injtime ) " & _
		 "VALUES " & _
		  "( ?, ?, ?, ? )", sqlcon)
		cmd.Parameters(0).Value = strinjnumber
		cmd.Parameters(1).Value = strEmpID
		cmd.Parameters(2).Value = dtInjDate
		cmd.Parameters(3).Value = dtInjDate

		sqlcon.Open()
		cmd.ExecuteNonQuery()
		sqlcon.Close()
	End Sub

End Class

This code was not tested.

tehNellie · Feb 22, 2012

It's also far more secure, the first example query was extremely vulnerable to SQL injection.

rkrause · Feb 22, 2012

lagbolt said:

Consider using SqlParameter objects as they appear in the Parameters collection of the SqlCommand object. In this case the data types are all handled by the system, and it is a much more readable process in your code...

PHP:

Imports System.Data
Imports System.Data.SqlClient
 
Public Class Class1
 
    Dim strinjnumber As String
    Dim strEmpID As String
    Dim dtInjDate As Date
    Dim dtInjTime As Date
 
    Sub Test1234()
        Dim sqlcon As New SqlConnection("server=tpmpi01;database=vq-dsi;uid=bk;password=valley")
        Dim cmd As New SqlCommand( _
         "INSERT INTO Injury " & _
          "(injnmbr, empID, injdate, injtime ) " & _
         "VALUES " & _
          "( ?, ?, ?, ? )", sqlcon)
        cmd.Parameters(0).Value = strinjnumber
        cmd.Parameters(1).Value = strEmpID
        cmd.Parameters(2).Value = dtInjDate
        cmd.Parameters(3).Value = dtInjDate
 
        sqlcon.Open()
        cmd.ExecuteNonQuery()
        sqlcon.Close()
    End Sub
 
End Class

This code was not tested.

what do you put in where there are ?'s??

MarkK · Feb 22, 2012

You use the "?" character. That's how it works. That indicates to the system that that item is parameterized and the value will be supplied later.

rkrause · Feb 22, 2012

Here is my code, and i also attached the picture of the error im getting with this code.

Protected Sub btnsubmit2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit2.Click

Dim intinjnumber As String
Dim intEmpID As String
Dim dtInjDate As Date
Dim dtInjTime As Date
Dim strOsha As String

intinjnumber = txtInjuryNumber.Text
intEmpID = txtEmployeeID.Text
dtInjDate = txtInjuryDate.Text
dtInjTime = txtInjuryTime.Text
strOsha = txtOsha.Text
Dim sqlcon As New SqlConnection("server=tpmpi01;database=vq-dsi;uid=bk;password=valley")
'checking if connection is open. if open close it and open again.
If sqlcon.State = ConnectionState.Open Then
sqlcon.Close()
End If
sqlcon.Open()

Dim cmd As New SqlCommand( _
"INSERT INTO Injury " & _
"(injnmbr, empID, injdate, injtime, osha ) " & _
"VALUES " & _
"(?, ?, ?, ?,?)", sqlcon)
cmd.Parameters(0).Value = intinjnumber
cmd.Parameters(1).Value = intEmpID
cmd.Parameters(2).Value = dtInjDate
cmd.Parameters(3).Value = dtInjTime
cmd.Parameters(4).Value = strOsha

sqlcon.Open()
cmd.ExecuteNonQuery()
sqlcon.Close()

End Sub

MarkK · Feb 25, 2012

I suggest you check SQL Server documentation for how to declare parameters then. Probably the '?' is not correct, because it appears from the error that the parameter count is zero when it should be 5. I don't know much about SQL Server SQL syntax.

dan-cat · Feb 25, 2012

First up never, ever ever insert values into a query without parameters. As mentioned you're asking for a sql injection attack which happened to this forum a while back.

The syntax for parameters is @parametername as below

Code:

Dim cmd As New SqlCommand( _
"INSERT INTO Injury " & _
"(injnmbr, empID, injdate, injtime, osha ) " & _
"VALUES " & _
"(@intinjnumber, @intEmpID, @dtInjDate, @dtInjTime, @strOsha)", sqlcon)

You're getting that error because you're trying to access the parameters collection of your sqlcommand object which is empty because you haven't added anything to it yet

So add a sqlparameter to the sqlparameters collection for each @parameter you have included in your sql string like below.

Code:

cmd.Parameters.Add(New SqlClient.SqlParameter("@intinjnumber", intinjnumber))
...

and execute your query

Code:

cmd.ExecuteNonQuery()

and you should be good to go.

ASP.net Code question (1 Viewer)

rkrause

Registered User.

MSAccessRookie

AWF VIP

rkrause

Registered User.

MSAccessRookie

AWF VIP

rkrause

Registered User.

MSAccessRookie

AWF VIP

rkrause

Registered User.

MarkK

bit cruncher

tehNellie

Registered User.

rkrause

Registered User.

MarkK

bit cruncher

rkrause

Registered User.

Attachments

MarkK

bit cruncher

dan-cat

Registered User.

Users who are viewing this thread