Solved

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

Posted on 2008-10-29
11
224 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
 

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
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: 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
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…

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now