Write information to Database

[Papa_Don]

Hi group,

I'm trying to store the information from a various textboxes into my database. However it is not writing the data (I'm getting my planned error message, "New Record FAILED. Please contact your systems administrator." From the code below, do you see where my error may be? What corrections do I need to make to ensure a successful insert?

Dim con As New SqlClient.SqlConnection
        con.ConnectionString = ("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Don\Documents\Visual Studio 2019\SalesForm\DWHRPT.mdf;Integrated Security=True")
        con.Open()
        Dim com As New SqlClient.SqlCommand("", con)
        com.CommandText = "INSERT INTO CUSTREC (CUST_ACCT_NO, CUST_COMPANY_NAME, CUST_LOCATION, CUST_TYPE, CUST_FIRST_NAME, CUST_MIDDLE_INITIAL, 
                                                  CUST_LAST_NAME, CUST_MAIL_ADDRESS1, CUST_MAIL_ADDRESS2, CUST_MAIL_CITY, CUST_MAIL_STATE, 
                                                  CUST_MAIL_ZIP_CODE, CUST_MAIL_ZIP_PLUS4, CUST_CREDIT_LIMIT, CUST_PRIMARY_PHONE, CUST_ALT_PHONE, 
                                                  CUST_TAX_EXEMPT_STATUS, CUST_TAX_EXEMPT_ID, CUST_SHIP_TO_ADDRESS1, CUST_SHIP_TO_ADDRESS2, 
                                                  CUST_SHIP_TO_CITY, CUST_SHIP_TO_STATE, CUST_SHIP_TO_ZIP_CODE, CUST_SHIP_TO_ZIP_PLUS4, CUST_USE_SELL_PRICE,
                                                  CUST_STD_DISC_PCNT, CUST_SHIP_TO_CONTACT_NAME, CUST_SHIP_TO_PHONE_NO, CUST_START_DATE) 
                            VALUES (@AcctNo, @CompName, @Loc, @Type, @FirstName, @MidInt, @LastName, @MailAddr1, @MailAddr2, @MailCity, @MailState, 
                                    @MailZip, @MailZipPlus4, @CreditLimit, @PrimaryPhone, @AltPhone, @TaxStatus, @TaxID, @ShipAddr1, @ShipAddr2,
                                    @ShipCity, @ShipState, @ShipZipCode, @ShipZipPlus4, @SellPrice, @Disc, @ShipContactName, @ShipToPhone, @StartDate)"

        Try
            com.Parameters.Add("@AcctNo", SqlDbType.BigInt).Value = tbxAccountNo.Text
            com.Parameters.Add("@CompName", SqlDbType.VarChar).Value = tbxCompanyName.Text
            com.Parameters.Add("@Loc", SqlDbType.Int).Value = tbxLoc.Text
            com.Parameters.Add("@Type", SqlDbType.VarChar).Value = tbxCustType.Text
            com.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = tbxFirstName.Text
            com.Parameters.Add("@MidInt", SqlDbType.VarChar).Value = tbxMiddleInt.Text
            com.Parameters.Add("@LastName", SqlDbType.VarChar).Value = tbxLastName.Text
            com.Parameters.Add("@MailAddr1", SqlDbType.VarChar).Value = tbxAddress1.Text
            com.Parameters.Add("@MailAddr2", SqlDbType.VarChar).Value = tbxAddress2.Text
            com.Parameters.Add("@MailCity", SqlDbType.VarChar).Value = tbxCity.Text
            com.Parameters.Add("@MailState", SqlDbType.VarChar).Value = tbxState.Text
            com.Parameters.Add("@MailZip", SqlDbType.VarChar).Value = zipCode
            com.Parameters.Add("@MailZipPlus4", SqlDbType.VarChar).Value = zipPlus4
            com.Parameters.Add("@CreditLimit", SqlDbType.Int).Value = tbxCreditLimit.Text
            com.Parameters.Add("@PrimaryPhone", SqlDbType.BigInt).Value = primephone
            com.Parameters.Add("@AltPhone", SqlDbType.BigInt).Value = altphone
            com.Parameters.Add("@TaxStatus", SqlDbType.VarChar).Value = tbxTaxExempt.Text
            com.Parameters.Add("@TaxID", SqlDbType.VarChar).Value = tbxTaxExemptID.Text
            com.Parameters.Add("@ShipAddr1", SqlDbType.VarChar).Value = tbxShipAddress1.Text
            com.Parameters.Add("@ShipAddr2", SqlDbType.VarChar).Value = tbxShipAddress2.Text
            com.Parameters.Add("@ShipCity", SqlDbType.VarChar).Value = tbxShipCity.Text
            com.Parameters.Add("@ShipState", SqlDbType.VarChar).Value = tbxShipState.Text
            com.Parameters.Add("@ShipZipCode", SqlDbType.VarChar).Value = ShipZipCode
            com.Parameters.Add("@ShipZipPlus4", SqlDbType.VarChar).Value = ShipZipPlus4
            com.Parameters.Add("@SellPrice", SqlDbType.Int).Value = Convert.ToInt32(tbxUseSellPriceNo.Text)
            com.Parameters.Add("@Disc", SqlDbType.Int).Value = Convert.ToInt32(tbxStdDiscPcnt.Text)
            com.Parameters.Add("@ShipContactName", SqlDbType.VarChar).Value = tbxShipToContactName.Text
            com.Parameters.Add("@ShipToPhone", SqlDbType.BigInt).Value = ShipPhone
            com.Parameters.Add("@StartDate", SqlDbType.VarChar).Value = tbxAcctStartDate.Text
            com.ExecuteNonQuery()
            MessageBox.Show("New Record Created", "", MessageBoxButtons.OK)
        Catch ex As System.Data.SqlClient.SqlException
            MessageBox.Show("New Record FAILED.  Please contact your systems administrator.", "", MessageBoxButtons.OK)
        End Try
        con.Close()

In advance, thanks for your help.

Don

[Reverend Jim]

It's been a few years since I've done database stuff but I suggest that instead of just displaying a generic error message like "New Record FAILED. Please contact your systems administrator." you display the actual error message returned in the exception. It should be one of the properties of the variable ex. It will likely give you more details.

And, if I may, I'd like to say again that these kind of questions do not belong in the community forum so I moved it to a more appropriate location.

[Papa_Don]

I'm not sure why but I'm not getting an error message. But I've looked and what I've attempted to write isn't there. I'll go back and step through it to see if I can find something and report back if needed.

My appologies for using the community forum. However the site has changed since the last time I used it (a couple of years ago) and I'm not sure how to direct inquiries to my fellow members here.

[Reverend Jim]

You are asked when you start a thread as to where you want to post it. Just pick programming from the drop down.

[Santanu.Das]

from my opinion: when you have tried to add parameters with command object by using parameters.Add() method you must have to convert the type of the value which you want to save in respect of the parameter type you declared in your code. Exception could be raised from your code lines 18 & 29: Text Type never be stored in int type.

You can use the method Parameter.AddWithValue(), in this method you have no need to declare the parameter type but you must convert the value in respect of field type of your database .