Solved

Code to insert data from a web form into a sql db table

Posted on 2008-10-29
11
225 Views
Last Modified: 2013-11-26
I am buidling a web application using Visual Studio 2005 in VB.net.  I have a web form that has several controls on it and I want to have a button that inserts the data entered in all the controls into a table in my SQL server db.  My connection settings are stored in my web.config file.  I cannot figure out how to refernce my saved connection setting in my web.config file and also, I am not sure that I have the code right for the other part of it.  I am brand new to VS and this is my first web app.  The class that I had signed up for to take was cancelled so I have been referencing a book and the Internet but have not had much luck.

I have attached my code so you can see what I have done.  Any help would be greatly appreciated. Please let me know if I need to reference anything in my file as well.

Thanks.

WEB.CONFIG FILE CODE:
<connectionStrings>
  <add name="CMSConnectionString" connectionString="Data Source=AUSSQL;Initial Catalog=CMS;User ID=timecard;Password=***"
   providerName="System.Data.SqlClient" />
  </connectionStrings>
 
WEB FORM CODE:
 
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oConn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("CMSConnectionString"))
        Dim cmd As New System.Data.SqlClient.SqlCommand()
        cmd.Connection = oConn
        oConn.Open()
        cmd.CommandText = "INSERT into Timecard(INEENO,INDTWE,INWKNO,INDYWK, INJBNO, INGLAN, INJCDI, INRGHR, INOVHR, INOTHR, INOTTY) values('" & EmplID1.Text & "','" & DatePicker1.DateValue & "','" & DD_DayofWeek.Text & "','" & jobno.Text & "','" & GLAcct.Text & "','" & DD_CostCode.Text & "','" & RegHrs.Text & "','" & OTHrs.Text & "','" & OthHrs.Text & "','" & DD_OthHourType.Text & "')"
        cmd.ExecuteNonQuery()
        oConn.Close()
 
 
    End Sub

Open in new window

0
Comment
Question by:imstac73
  • 6
  • 5
11 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 22839077
Hi,
You should be using ConfigurationManager.ConnectionStrings collection rather than AppSettings. But make sure that you have saved the connection string in a proper section in web.config.
Also, the single quote format
' " & txtname.text & " '
is used with string columns. I just wanted to clarify this as you have used single quotes for all fields.
0
 

Author Comment

by:imstac73
ID: 22840705
Okay, so I changed the code to use connectionstrings but I am now getting an error that my oConn variable is not declared.  Am I still using the wrong code for my connection declaration?  I attached my new code.

Also, thanks for the tip on the string thing; I didn't need it for all the fields.
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oConn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CMSConnectionString").ConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand()
        cmd.Connection = oConn
        oConn.Open()
        cmd.CommandText = "INSERT into Timecard(INEENO,INDTWE,INWKNO,INDYWK, INJBNO, INGLAN, INJCDI, INRGHR, INOVHR, INOTHR, INOTTY) values('" & EmplID1.Text & "',DatePicker1.text,'" & DD_DayofWeek.Text & "',jobno.Text,GLAcct.Text,DD_CostCode.Text,RegHrs.Text,OTHrs.Text,OthHrs.Text,'" & DD_OthHourType.Text & "')"
        cmd.ExecuteNonQuery()
        oConn.Close()
 
 
    End Sub

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 22841125
Does the connection string exist in the proper section? For example


  <connectionStrings>    
    <add name="DBConnect" connectionString="Data Source=;Initial Catalog=;Persist Security Info=True;User ID=;Password=" providerName="System.Data.SqlClient"/>
  </connectionStrings>
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:imstac73
ID: 22841270
Yes. Actually after I saved it the errors went away so its working now.  However, when it gets to my insert statement I get the following error: "The name 'text' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."

I'm not sure how I'm supposed to reference the value that the user keys in the my textboxes and selects from the comboboxes.  I tried using EmplID1.value too and it didn't like that either.

Here is what I changed the code to after you told me about not using the string code.

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oConn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CMSConnectionString").ConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand()
        cmd.Connection = oConn
        oConn.Open()
        cmd.CommandText = "INSERT into Timecard(INEENO,INDTWE,INWKNO,INDYWK, INJBNO, INGLAN, INJCDI, INRGHR, INOVHR, INOTHR, INOTTY) values(EmplID1.text,DatePicker1.text,DD_DayofWeek.Text,jobno.Text,GLAcct.Text,DD_CostCode.Text,RegHrs.Text,OTHrs.Text,OthHrs.Text,DD_OthHourType.Text)"
        cmd.ExecuteNonQuery()
        oConn.Close()
 
 
    End Sub

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 22841320
Oops. I think you reacted to my suggestion about single quotes. Replace this insert statement with the one you originally had and try executing again.
0
 

Author Comment

by:imstac73
ID: 22841535
Okay, I put it back and now I get this error "Error converting data type varchar to numeric".  I have a question..if my table field is a decimal field type do I need to put some kind of setting in my textbox properties to reflect this?
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 200 total points
ID: 22841807
That was the reason for my original comment about single quotes. Check in the database and for all fields with datatype varchar use single quotes but with fields with numeric datatype do not use SINGLE quotes and i do not mean to remove double quotes as well. For example

"Insert Into Person(Id, Name) Values(" & txtid.text & ", '" & txtname.text & "')

notice here that there is a single then double and double then single qoute around txtname.text
0
 

Author Comment

by:imstac73
ID: 22842375
Okay, so I changed the quotes as need but now I'm getting the error "incorrect syntax near ',' "  I went through to see if I was missing any quotes but didn't see anything.


"INSERT into Timecard(INEENO,INDTWE,INWKNO,INDYWK, INJBNO, INGLAN, INJCDI, INRGHR, INOVHR, INOTHR, INOTTY) values(" & EmplID1.Text & ",'" & DatePicker1.TextValue & "'," & DD_week.Text & "," & DD_DayofWeek.Text & ",'" & jobno.Text & "','" & GLAcct.Text & "','" & DD_CostCode.Text & "'," & RegHrs.Text & "," & OTHrs.Text & "," & OthHrs.Text & ",'" & DD_OthHourType.Text & "')"

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 22842586
Setup a breakpoint on the code and see what is the final SQL being generated and if it is correct.
0
 

Author Comment

by:imstac73
ID: 22843474
How do I do that?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 22844607
google would help you learn that.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

774 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question