VB in ASP.NET- Writing Variables to an MSSQL Database

[bazeem]

I am extremely new to programming and Visual Basic.

I want to know how to write/insert variables into an MS SQL database. When I say variables I mean input from textboxes and the current date/time generated.

I am currently using this code which I got from a tutorial and it basically writes the defined constants to the database.

Sub AddRecord() Dim sConnectionString As String _
 
 
= "User ID=********;Password='*****';Initial Catalog=******;Data Source=p3swhsql-v16.shr.phx3.secureserver.net"
Dim objConn As New SqlConnection(sConnectionString)
objConn.Open()
Dim sSQL As String = "INSERT INTO Employee " & _
"(emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date)" & _
"VALUES ('EBS123', 'Ebrahim', 'M', 'E', " & _
"10, 82,'0877','2001-01-01')"
 
Dim objCmd As New SqlCommand(sSQL, objConn)
Try
objCmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine(e.Message)
Dim t = (e.Message)
Literal1.Text = t
Literal3.Text = "ERROR"
End Try
Console.WriteLine("Record Added")Literal2.Text = "RECORD ADDED"
 
End Sub
 

(Literal1 and Literal2 are two literals I have on the page to display the Error Received/IF record added)


But when I try to get the same script to insert variables from textboxes, I get an error saying

The name "empvar" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted

 
Dim empvar,fnameVar,lnameVar
empvar=TextBox1.Text
. ..
Dim sSQL As String = "INSERT INTO Employee " & _
"(emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date)" & _
"VALUES (empvar,fnameVar,lnameVar, " & _
"10, 82,'0877','2001-01-01')"
 

Could you please assist me by telling me what I have done wrong in my script; or if the entire script does not allow for writing variables then could you tell me a simple one which can do that.

Thanking you very much in advanced

[DrBunchman]

Hi bazeem,

You need to put your VB variables outside the SQL string like this:

 
"VALUES ('" & empvar & "', '" & fnameVar & "', '" & lnameVar "' "
 

In this way the value of the variables empvar, fnameVar & lnameVar will be passed into the SQL String. Note the single quotes that surround each variable - these are required if the database column is a string.

Does this make sense?

Dr B

[kenobewan]

Suggestion, strongly advise against inserting id this way can lead to errors and probs later - not scalable.

[Curtis Rutland]

I agree with Kenobewan. Take a look at this thread. The 6th post. I give the OP an example of how to use a parameterized query. This is a better way to do it.

[DrBunchman]

I agree also.

I actually thought Kenobewan's reply was referring to the OP inserting an ID column rather than the non-use of parameters..?

Dr B

[Curtis Rutland]

I think you are right. I read it too quickly. Still, parameters are easier and more elegant than building strings.

[DrBunchman]

They certainly are. I was aiming to keep my reply in context with the tutorial the OP had been looking at but I guess it makes sense to get into good habits early on!

Dr B

[kenobewan]

Agree that parameters are better practice. If the table is employee and OP refers to emp_id I would want to that up as the primary key not for replication, default 1 increment 1. I never let the application set the id of the table, if this is indeed the situation :o).