By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,103 Members | 646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,103 IT Pros & Developers. It's quick & easy.

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

P: 3
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.

Expand|Select|Wrap|Line Numbers
  1. Sub AddRecord() Dim sConnectionString As String _
  2.  
  3.  
  4. = "User ID=********;Password='*****';Initial Catalog=******;Data Source=p3swhsql-v16.shr.phx3.secureserver.net"
  5. Dim objConn As New SqlConnection(sConnectionString)
  6. objConn.Open()
  7. Dim sSQL As String = "INSERT INTO Employee " & _
  8. "(emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date)" & _
  9. "VALUES ('EBS123', 'Ebrahim', 'M', 'E', " & _
  10. "10, 82,'0877','2001-01-01')"
  11.  
  12. Dim objCmd As New SqlCommand(sSQL, objConn)
  13. Try
  14. objCmd.ExecuteNonQuery()
  15. Catch e As Exception
  16. Console.WriteLine(e.Message)
  17. Dim t = (e.Message)
  18. Literal1.Text = t
  19. Literal3.Text = "ERROR"
  20. End Try
  21. Console.WriteLine("Record Added")Literal2.Text = "RECORD ADDED"
  22.  
  23. 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
Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim empvar,fnameVar,lnameVar
  3. empvar=TextBox1.Text
  4. . ..
  5. Dim sSQL As String = "INSERT INTO Employee " & _
  6. "(emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date)" & _
  7. "VALUES (empvar,fnameVar,lnameVar, " & _
  8. "10, 82,'0877','2001-01-01')"
  9.  
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
Jul 27 '08 #1
Share this Question
Share on Google+
7 Replies

DrBunchman
Expert 100+
P: 979
Hi bazeem,

You need to put your VB variables outside the SQL string like this:
Expand|Select|Wrap|Line Numbers
  1.  
  2. "VALUES ('" & empvar & "', '" & fnameVar & "', '" & lnameVar "' "
  3.  
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
Jul 28 '08 #2

kenobewan
Expert 2.5K+
P: 4,871
Suggestion, strongly advise against inserting id this way can lead to errors and probs later - not scalable.
Jul 28 '08 #3

Curtis Rutland
Expert 2.5K+
P: 3,256
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.
Jul 28 '08 #4

DrBunchman
Expert 100+
P: 979
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
Jul 28 '08 #5

Curtis Rutland
Expert 2.5K+
P: 3,256
I think you are right. I read it too quickly. Still, parameters are easier and more elegant than building strings.
Jul 28 '08 #6

DrBunchman
Expert 100+
P: 979
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
Jul 28 '08 #7

kenobewan
Expert 2.5K+
P: 4,871
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).
Jul 30 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.