| Author |
 Topic  |
|
|
GaryM_1983
Starting Member
United Kingdom
10 Posts |
 Posted - 06/07/2012 : 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 |
Edited by - GaryM_1983 on 06/07/2012 07:29:04
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 06/07/2012 : 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
United Kingdom
10 Posts |
Posted - 06/07/2012 : 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
India
311 Posts |
Posted - 06/07/2012 : 08:54:11
|
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.
Vijay is here to learn something from you guys. |
Edited by - vijays3 on 06/07/2012 08:55:03 |
|
|
|
GaryM_1983
Starting Member
United Kingdom
10 Posts |
Posted - 06/07/2012 : 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
153 Posts |
Posted - 06/07/2012 : 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
United Kingdom
10 Posts |
Posted - 06/07/2012 : 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
Flowing Fount of Yak Knowledge
3826 Posts |
Posted - 06/07/2012 : 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 |
Edited by - Lamprey on 06/07/2012 10:49:02 |
|
|
|
GaryM_1983
Starting Member
United Kingdom
10 Posts |
Posted - 06/07/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 06/07/2012 : 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. |
|
|
| |
Topic |
|
Blank fields being added to database - why?
