Blank fields being added to database - why? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Blank fields being added to database - why?

Author

Topic

GaryM_1983
Starting Member

10 Posts

Posted - 2012-06-07 : 07:05:06

Hello,

I am a student in the UK studying Visual Basic and SQL Server.

I have written the code I require to add records to a customer table, yet when I run the program, the database adds an extra row and entering an autonumber for customer id, yet all of the other fields are blank.
I just can't seem to crack it. It is strange as the code I used is identical (except field names of course) to another insert query which is working fine.

Any advice would be very much appreciated!

Thanks

GaryM_1983

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-07 : 07:53:12

Now please think about it as if it was my problem und my code and you can't see what I have coded or whatever. Would you be able to help me with the given information?

No, you're never too old to Yak'n'Roll if you're too young to die.

GaryM_1983
Starting Member

10 Posts

Posted - 2012-06-07 : 08:00:09

This is the full code for the page on VB

Imports System.Data.SqlClient
Imports System.Data

Public Class frmAddCustomer

Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim icount As Integer
Dim SQLstr As String
Dim SQLstrSale As String
Dim myCommandSale As SqlCommand
Dim ds As New DataSet
Dim adapter As New SqlDataAdapter
Dim inc As Integer
Dim rownumber As Integer

Dim FirstName As String
Dim Surname As String
Dim Add1 As String
Dim Add2 As String
Dim Add3 As String
Dim PostCode As String
Dim TelNo As String

Private Sub frmAddCustomer_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'ClassicCarsDealershipDataSet1.tblCustomer' table. You can move, or remove it, as needed.
Me.TblCustomerTableAdapter.Fill(Me.ClassicCarsDealershipDataSet1.tblCustomer)

txtFirstname.Text = FirstName
txtSurname.Text = Surname
txtAddress1.Text = Add1
txtAddress2.Text = Add2
txtAddress3.Text = Add3
txtPostCode.Text = PostCode
txtTelNo.Text = TelNo

End Sub

Private Sub btnSubmit_Click(sender As System.Object, e As System.EventArgs) Handles btnSubmit.Click

myConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=F:\GC211 - Advanced Programming\Assignment\ClassicCarDealership\ClassicCarDealership\ClassicCarsDealership.mdf;Integrated Security=True;User Instance=True")
myConnection.Open()
SQLstr = "INSERT INTO tblCustomer([Firstname], [Surname], [Address1], [Address2], [Address3], [PostCode], [TelephoneNumber]) VALUES ('" & FirstName & "', '" & Surname & "', '" & Add1 & "', '" & Add2 & "', '" & Add3 & "', '" & PostCode & "', ' " & TelNo & " ')"
myCommand = New SqlCommand(SQLstr, myConnection)
icount = myCommand.ExecuteNonQuery
MessageBox.Show("Done")
myConnection.Close()

End Sub

Private Sub TblCustomerBindingNavigatorSaveItem_Click(sender As System.Object, e As System.EventArgs) Handles TblCustomerBindingNavigatorSaveItem.Click
Me.Validate()
Me.TblCustomerBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.ClassicCarsDealershipDataSet1)

End Sub
End Class

GaryM_1983

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-06-07 : 08:54:11

[code]
What is your table schema for tblCustomer?

I guess you have set identity property for
any of the column So when ever you run this
program without giving any other data for
remaining column it insert null for all
those field and increment by 1 to column
which has set identity.

[/code]

Vijay is here to learn something from you guys.

GaryM_1983
Starting Member

10 Posts

Posted - 2012-06-07 : 09:26:34

Exactly, my fields are [Customer_ID] (PK and Identity), [Firstname], [Surname], [Address1], [Address2], [Address3], [PostCode], TelephoneNumber]

My confirmation message displays and a row is added, but as you said the information in my fields is NULL.

GaryM_1983

uberman
Posting Yak Master

159 Posts

Posted - 2012-06-07 : 10:04:33

looks like you are not setting the values of the variables being passed to the sql string, should
('" & FirstName & "', '" & Surname & "',....
actually be
('" & FirstName.Text & "', '" & Surname.Text & "',..?

Anyway, check the values coming in from the form and echo out/debug out "SQLstr" so you can see the sql string you have created before it is executed.

You might also want to read up on Sql Injection

GaryM_1983
Starting Member

10 Posts

Posted - 2012-06-07 : 10:21:14

Hi Uberman,
When I add .Text as your example shows, an Error shows " 'Text' is not a member of 'String'

GaryM_1983

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-07 : 10:47:14

You don't ever seem to assing the values from the form to your variables. For example:

FirstName = txtFirstname.Text
Surname = txtSurname.Text
.. etc

or you could just use the form values directly:

SQLstr = "INSERT INTO tblCustomer([Firstname], [Surname], [Address1], [Address2], [Address3], [PostCode], [TelephoneNumber]) 
VALUES ('" & txtFirstname.Text & "', '" & txtSurname.Text & .. etc

GaryM_1983
Starting Member

10 Posts

Posted - 2012-06-07 : 11:14:08

Lamprey, I have followed your second example and I am up and running!

Thank you all for your help!

Phew, it's tough being a newbie!

GaryM_1983

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-07 : 13:56:12

nice

as you can see - posting the required information was important!

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources