Populating Data

[ThomasM]

Can someone tell me what I am missing... I keep getting this error when trying to write to the database

Thank you!!!!!

Parameterized Query '(@WorkSheetID nvarchar(14),@Origin nvarchar(5),@DealerName nvarc' expects parameter @Address, which was not supplied.

[FONT=Arial, Helvetica, Geneva, SunSans-Regular, sans-serif]Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Parameterized Query '(@WorkSheetID nvarchar(14),@Origin nvarchar(5),@DealerName nvarc' expects parameter @Address, which was not supplied.

Source Error:


Line 401: .Add(New SqlParameter("@DOB", sDOB))Line 402: End WithLine 403: ra = myCommand.ExecuteNonQuery()Line 404: myConnection.Close()Line 405: 'Lets Check to see if there is a Co Applicant


Here is my Code:

Dim ra As Integer
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim connString As String = "Server=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|CustomerInfo.mdf;Integrated Security=True;User Instance=True"
Dim CPhoneNum, CWorkNum, CCellNum, sWorkSheetNum, sSaleOrigin As String
Dim sDealerShipName, sBkupSlsRep, sDateCreated, sFirstName, sMiddleName, slastname, sBuyAddress, sSSN, sDOB As String
Dim scity, sState, sZip, sEmailAddres, sBuyersCounty, sSalesTax As String
Dim SlsManLoginN As String
Dim myPrincipal As IPrincipal = Me.User
'Customer Information
sWorkSheetNum = lblCustWksnum.Text
sSaleOrigin = lblSaleOrigin.Text
sDealerShipName = lblDealerShipName.Text
sDateCreated = lblDateCreated.Text
sBkupSlsRep = lblBkupSlsRep.Text
sFirstName = lblcFirstName.Text
sMiddleName = lblcMiddleName.Text
slastname = lblcLastName.Text
sBuyAddress = rBuyAddress
scity = rcity
sState = rState
sZip = rZip
sEmailAddres = lblEmailAddres.Text
sBuyersCounty = lblBuyersCounty.Text
sSalesTax = lblSalesTax.Text
sSSN = lblSSN.Text
sDOB = lblDOB.Text
'Lets get the Current Logged on User
SlsManLoginN = lblSlsRep.Text
myConnection = New SqlConnection
myConnection.ConnectionString = connString
myConnection.Open()
CPhoneNum = lblHnum.Text
CWorkNum = lblWnum.Text
CCellNum = lblCnum.Text
Const sql As String = "Insert into CustomerTable (WorkSheetID, Origin, DealerName, DateCreated, SalesRep, " _
+ "BkupSalesRep, FirstName, MiddleName, LastName, Address, City, State, Zip, HomePhone, WorkPhone, " _
+ "CellPhone, Email, County, SalesTax, SS#, DOB) Values (@WorkSheetID, @Origin, @DealerName, @DateCreated, " _
+ "@SalesRep, @BkupSalesRep, @FirstName, @MiddleName, @LastName, @Address, @City, @State, @Zip, @HomePhone, @WorkPhone, @CellPhone, @Email, @County, @SalesTax, @SS#, @DOB)"
myCommand = New SqlCommand(sql, myConnection)
With myCommand.Parameters
.Add(New SqlParameter("@WorkSheetID", sWorkSheetNum))
.Add(New SqlParameter("@Origin", sSaleOrigin))
.Add(New SqlParameter("@DealerName", sDealerShipName))
.Add(New SqlParameter("@DateCreated", sDateCreated))
.Add(New SqlParameter("@SalesRep", SlsManLoginN))
.Add(New SqlParameter("@BkupSalesRep", sBkupSlsRep))
.Add(New SqlParameter("@FirstName", sFirstName))
.Add(New SqlParameter("@MiddleName", sMiddleName))
.Add(New SqlParameter("@LastName", slastname))
.Add(New SqlParameter("@Address", sBuyAddress))
.Add(New SqlParameter("@City", scity))
.Add(New SqlParameter("@State", sState))
.Add(New SqlParameter("@Zip", sZip))
.Add(New SqlParameter("@HomePhone", CPhoneNum))
.Add(New SqlParameter("@WorkPhone", CWorkNum))
.Add(New SqlParameter("@CellPhone", CCellNum))
.Add(New SqlParameter("@Email", sEmailAddres))
.Add(New SqlParameter("@County", sBuyersCounty))
.Add(New SqlParameter("@SalesTax", sSalesTax))
.Add(New SqlParameter("@SS#", sSSN))
.Add(New SqlParameter("@DOB", sDOB))
End With
ra = myCommand.ExecuteNonQuery()
myConnection.Close()
[/FONT]

 

[TechGnome]

Replace "SS#" with "[SS#]" in the insert and remove the # form the @SS# parameter name. It's all I can think of....

-tg

 

[ThomasM]

That didnt work but thank you for trying!!!

 

[TechGnome]

Well, color me cornfused....

Try
  ra = myCommand.ExecuteNonQuery()
Catch exSQL As SqlException
  MessageBox.Show exSQL.ToString
End Try
myConnection.Close()

What does the message box tell you? Sometimes by trapping for the SQL error itself you can get more info than you can from the system exception (which is what's typicaly displayed when something goes wrong).

-tg

 

[ThomasM]

I have made some code cahanges since my last post.
My Current error is.... I am assuming on of the strings is two long for nVChar...


String or binary data would be truncated.
The statement has been terminated

Here is the Code:
Dim myCommand As SqlCommand
Dim myConnection As SqlConnection
Dim insertCmd As String

myConnection = New SqlConnection("Server=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|CustomerInfo.mdf;Integrated Security=True;User Instance=True")
insertCmd = "Insert into CustomerTable Values (@WorkSheetID, @Origin, @DealerName, @DateCreated, " _
& "@SalesRep, @BkupSalesRep, @FirstName, @MiddleName, @LastName, @Address, @City, @State, " _
& "@Zip, @HomePhone, @WorkPhone, @CellPhone, @Email, @County, @SalesTax, @SS, @DOB)"
myCommand = New SqlCommand(insertCmd, myConnection)
myCommand.Parameters.Add(New SqlParameter("@WorkSheetID", Data.SqlDbType.NVarChar))
myCommand.Parameters("@WorkSheetID").Value = lblCustWksnum.Text
myCommand.Parameters.Add(New SqlParameter("@Origin", Data.SqlDbType.NVarChar))
myCommand.Parameters("@Origin").Value = lblSaleOrigin.Text
myCommand.Parameters.Add(New SqlParameter("@DealerName", Data.SqlDbType.Char))
myCommand.Parameters("@DealerName").Value = lblDealerShipName.Text
myCommand.Parameters.Add(New SqlParameter("@DateCreated", Data.SqlDbType.NVarChar))
myCommand.Parameters("@DateCreated").Value = lblDateCreated.Text
myCommand.Parameters.Add(New SqlParameter("@SalesRep", Data.SqlDbType.NVarChar))
myCommand.Parameters("@SalesRep").Value = lblSlsRep.Text
myCommand.Parameters.Add(New SqlParameter("@BkupSalesRep", Data.SqlDbType.NVarChar))
myCommand.Parameters("@BkupSalesRep").Value = lblBkupSlsRep.Text
myCommand.Parameters.Add(New SqlParameter("@FirstName", Data.SqlDbType.NVarChar))
myCommand.Parameters("@FirstName").Value = lblcFirstName.Text
myCommand.Parameters.Add(New SqlParameter("@MiddleName", Data.SqlDbType.NVarChar))
myCommand.Parameters("@MiddleName").Value = lblcMiddleName.Text
myCommand.Parameters.Add(New SqlParameter("@LastName", Data.SqlDbType.NVarChar))
myCommand.Parameters("@LastName").Value = lblcLastName.Text
myCommand.Parameters.Add(New SqlParameter("@Address", Data.SqlDbType.NVarChar))
myCommand.Parameters("@Address").Value = lblrAddress.Text
myCommand.Parameters.Add(New SqlParameter("@City", Data.SqlDbType.NVarChar))
myCommand.Parameters("@City").Value = lblrCity.Text
myCommand.Parameters.Add(New SqlParameter("@State", Data.SqlDbType.NVarChar))
myCommand.Parameters("@State").Value = lblrState.Text
myCommand.Parameters.Add(New SqlParameter("@Zip", Data.SqlDbType.NVarChar))
myCommand.Parameters("@Zip").Value = lblrZip.Text
myCommand.Parameters.Add(New SqlParameter("@HomePhone", Data.SqlDbType.NVarChar))
myCommand.Parameters("@HomePhone").Value = lblHnum.Text
myCommand.Parameters.Add(New SqlParameter("@WorkPhone", Data.SqlDbType.NVarChar))
myCommand.Parameters("@WorkPhone").Value = lblWnum.Text
myCommand.Parameters.Add(New SqlParameter("@CellPhone", Data.SqlDbType.NVarChar))
myCommand.Parameters("@CellPhone").Value = lblCnum.Text
myCommand.Parameters.Add(New SqlParameter("@Email", Data.SqlDbType.NVarChar))
myCommand.Parameters("@Email").Value = lblEmailAddres.Text
myCommand.Parameters.Add(New SqlParameter("@County", Data.SqlDbType.NVarChar))
myCommand.Parameters("@County").Value = lblBuyersCounty.Text
myCommand.Parameters.Add(New SqlParameter("@SalesTax", Data.SqlDbType.NVarChar))
myCommand.Parameters("@SalesTax").Value = lblSalesTax.Text
myCommand.Parameters.Add(New SqlParameter("@SS", Data.SqlDbType.NVarChar))
myCommand.Parameters("@SS").Value = lblSSN.Text
myCommand.Parameters.Add(New SqlParameter("@DOB", Data.SqlDbType.NVarChar))
myCommand.Parameters("@DOB").Value = lblDOB.Text
myCommand.Connection.Open()

Try
myCommand.ExecuteNonQuery()
ShowMessageBox(Me, "Record Added " & insertCmd)
Catch ex As SqlException
If ex.Number = 2627 Then
ShowMessageBox(Me, "ERROR: A record already exists with " _
& "the same primary key")
Else
ShowMessageBox(Me, "ERROR: Could not add record")
End If
End Try
myCommand.Connection.Close()

 

[TechGnome]

That means that the data being passed in is longer than the field will allow in the table.
Solution: For string parameters, set the size property of the parameter.

-tg

 

[ThomasM]

amazing how that works....

Successfully populating !!!

Many Thanks