Solved

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

Posted on 2008-10-29
11
223 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:imstac73
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
How do I do that?
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
google would help you learn that.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

After several hours of googling I could not gather any information on this topic. There are several ways of controlling the USB port connected to any storage device. The best example of that is by changing the registry value of "HKEY_LOCAL_MACHINE\S…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
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…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 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

13 Experts available now in Live!

Get 1:1 Help Now