Solved

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

Posted on 2008-10-29
11
226 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

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

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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 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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 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