• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

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

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
imstac73
Asked:
imstac73
  • 6
  • 5
1 Solution
 
CodeCruiserCommented:
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
 
imstac73Author Commented:
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
 
CodeCruiserCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
imstac73Author Commented:
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
 
CodeCruiserCommented:
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
 
imstac73Author Commented:
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
 
CodeCruiserCommented:
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
 
imstac73Author Commented:
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
 
CodeCruiserCommented:
Setup a breakpoint on the code and see what is the final SQL being generated and if it is correct.
0
 
imstac73Author Commented:
How do I do that?
0
 
CodeCruiserCommented:
google would help you learn that.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now