Posted on

Write information to Database

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

Posted on

Inserting Data into SQL using VB

Hi this is my first time using this site, so please bare with me. I'm also pretty new at coding and got thrown into connecting to a database in one of my classes. But anywho, I keep receiving a syntax error and I was wondering if anyone could help me figure out why. Thank you!

Imports System.Data
Imports System.Data.SqlClient

Public Class PatientForm1

    Dim con As New SqlClient.SqlConnection("deleted for privacy")
    Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles lname.TextChanged

    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub

    Private Sub Label14_Click(sender As Object, e As EventArgs) Handles Label14.Click

    End Sub

    Private Sub Label10_Click(sender As Object, e As EventArgs) Handles Label10.Click

    End Sub

    Private Sub TextBox12_TextChanged(sender As Object, e As EventArgs) Handles TextBox12.TextChanged

    End Sub

    Private Sub TextBox20_TextChanged(sender As Object, e As EventArgs) Handles TextBox20.TextChanged

    End Sub

    Private Sub Label1_Click(sender As Object, e As EventArgs) Handles Label1.Click

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles bn1.Click

        con.Open()
        Dim query As New SqlCommand(("INSERT INTO Table VALUES ('" & PatientID.Text & "', '" & fname.Text & "'," & lname.Text & "')"), con)
        'Dim mycommand As New SqlCommand(query, con)

        If query.ExecuteNonQuery() Then
            MessageBox.Show("Patient Added")
        Else
            MessageBox.Show("Error patient wasn't added.")
        End If

        con.Close()

        'EditAdd.Show()
        'Me.Close()
    End Sub

    Private Sub Label21_Click(sender As Object, e As EventArgs) Handles Label21.Click

    End Sub

    Private Sub TextBox3_TextChanged(sender As Object, e As EventArgs) Handles fname.TextChanged

    End Sub

    Private Sub bn2_Click(sender As Object, e As EventArgs) Handles bn2.Click
        Me.Close()
        EditAdd.Show()
    End Sub

    Private Sub Label3_Click(sender As Object, e As EventArgs) Handles Label3.Click

    End Sub

End Class
Posted on

Click on Listbox name to fill Textboxes with Username and Password

I want when click on the Name Listed on a Listbox from the database to fill Textboxes with Username and Password of particular user from SQL database.

I am able to see Names stored on the database on the listbox but when i click on the name its unable to fill the Textboxes.

Here is the code that fills the Listbox with data from database (NOTE: I am able to view a list of names on the Listbox with the code below)

 Public Sub ListBox()
    Try
        If con.State = ConnectionState.Open Then
            con.Close()

        End If
        con.Open()
        Dim cmd As SqlCommand
        cmd = con.CreateCommand()
        cmd.CommandText = "SELECT * FROM Admin"
        Dim Reader As SqlDataReader
        Reader = cmd.ExecuteReader
        While Reader.Read
            lbAdmin.Items.Add(Reader.Item("Full Name"))
        End While
    Catch ex As Exception
    End Try
End Sub

And here is the code I want to fill the Textboxes after clicking on a name in a Listbox (NOTE: Unable to fill Textboxes with code below). I really dont know where I am going wrong, please help.

Private Sub lbAdmin_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles lbAdmin.SelectedIndexChanged
    Try
        If con.State = ConnectionState.Open Then
            con.Close()
        End If
        con.Open()

        Dim cmd As SqlCommand
        cmd = con.CreateCommand()
        cmd.CommandText = "SELECT * FROM Admin"
        Dim Reader As SqlDataReader
        Reader = cmd.ExecuteReader
        While Reader.Read
            txtfullname.Text = Reader.GetString("Full Name")
            txtusername.Text = Reader.GetString("Username")
            txtpassword.Text = Reader.GetString("Password")
        End While
    Catch ex As Exception
    End Try
End Sub