Can't insert data into the database

[Aven.Seven]

Greetings guys i'm stuck in this problem for 2 days now can you guy tell me the problem?

here's the code

If errorMsgLbl.Text = "Password match" Then
                sqlCmd = New SqlCommand("INSERT INTO registered_cust_tbl([customer_fname], [customer_lname], [customer_DoB], [customer_Contact], [customer_Email], " & _
                " [customer_Address], [customer_Gender], [customer_Ailments], [customer_Username], [customer_Password], " & _
                " [customer_Secret_Question], [customer_Secret_Answer], [customer_Purpose], [customer_weightRcnt], [customer_heightRcnt], [customer_bmi], [customer_weightLossGain], [customer_weightUpdt]) " & _
                " VALUES ('" & firstNameTb.Text.Trim & "', '" & lastNameTb.Text.Trim & "', '" & DoBdtp.Text & "', '" & customerMPNo.Text & "', '" & _
                               emailTb.Text.Trim & "', '" & addressRtb.Text.Trim & "', '" & gender & "', '" & ailmentstxtBx.Text.Trim & "', '" & _
                               usernameTF.Text.Trim & "', '" & passwordTF.Text & "', '" & SecQuesBox.Text & "', '" & SecAnsBox.Text.Trim & "', '" & _
                               purpose & "', '" & numWeight.Text & " " & TypeOfWeight.Text & "', '" & numHeight.Text & " " & TypeOfHeight.Text & "', '" & TotalBmi.Text & "', '" & "0" & "' , '" & "0" & "' )", getConnect())

                errorMsgLbl.Text = "Password match"
                msgLbl.Text = "Password match"
                msgLbl.ForeColor = Color.DarkGreen
                errorMsgLbl.ForeColor = Color.DarkGreen

                'sqlCmd.ExecuteNonQuery()
                MsgBox("You have successfully registered.")
                getConnect.Close()
            Else
            End If

I don't know the problem here.

[Begginnerdev]

I know one that you can clean that code up: DataAdapters

For instance:

Private Sub IssueInsert()
    Dim cmd As New SQLCommand("SELECT * FROM registered_cust_tbl",myCon)
    Dim da As New SQLDataAdapter(cmd)
    Dim ds As New DataSet
    Try
        da.Fill(ds,"tblRegCust")

        If IsNothing(ds.Tables("tblRegCust")) = False Then
            Dim dr As New DataRow = ds.Tables("tblRegCust").Rows.Add

            'Put your column names here:
            dr("customer_fname") = firstNameTb.Text.Trim
            dr("customer_lname") = lastNameTb.Text.Trim
            dr("customer_DoB") = DoBtb.Text
            '........

            ds.Tables("tblRegCust").Rows.Add(dr)

            da.UpdateCommand = New SQLCommandBuilder(da).GetUpdateCommand()
            da.Update(ds)
        Else
            MsgBox("Nothing was returned from the table.")
        End If
    Catch ex As Exception
        MsgBox(ex.ToString)
    Finally
        cmd = Nothing
        dat = Nothing
        ds = Nothing
    End Try        
End Sub

This will be protected against SQL injection attacks.

[Aven.Seven]

There is an error in dr. The error say's "is not accessible in this context because it's protected friend".

[Hazuan Nazri]

You can try like this, if i wrong please inform me

Before Public Class you need to insert this code:

Module Koneksi
    Public conn As SqlClient.SqlConnection
    Public Function GetConnect()
        conn = New SqlClient.SqlConnection("Data Source=YOUR PC NAME"YOUR-pc"\SQLEXPRESS;initial catalog = YOUR DATABASE NAME;Integrated Security = True")
        Return conn
    End Function
End Module

and then put this code where you want

Dim check As Integer
        Dim conn As SqlClient.SqlConnection
        Dim cmdCustomer As New SqlClient.SqlCommand
        Dim cmdCustomer1 As New SqlClient.SqlCommand
        Dim daCustomer As New SqlClient.SqlDataAdapter
        Dim dsCustomer As New DataSet
        Dim dtCustomer As New DataTable


            If errorMsgLbl.Text = "Password match" Then

                Try
                    conn = GetConnect()

                    conn.Open()
                    cmdCustomer = conn.CreateCommand
                    cmdCustomer.CommandText = "SELECT * FROM registered_cust_tbl WHERE customer_Email ='" & Trim(emailTb.Text) & " ' "
                    daCustomer.SelectCommand = cmdCustomer
                    daCustomer.Fill(dsCustomer, "registered_cust_tbl")
                    dtCustomer = dsCustomer.Tables("registered_cust_tbl")
                    If (dtCustomer.Rows.Count > 0) Then
                        MsgBox("this Email " & Trim(emailTb.Text) & " is already in use!", MsgBoxStyle.OkOnly, "Message :")
                    Else
                        Dim dr As DataRow = dsCustomer.Tables("registered_cust_tbl").NewRow
                        cmdCustomer1 = conn.CreateCommand
                        cmdCustomer1.CommandText = "INSERT INTO registered_cust_tbl(customer_fname, customer_lname, customer_DoB, customer_Contact, customer_Email, customer_Address, customer_Gender, customer_Ailments, customer_Username, customer_Password, customer_Secret_Question, customer_Secret_Answer, customer_Purpose, customer_weightRcnt, customer_heightRcnt, customer_bmi, customer_weightLossGain, customer_weightUpdt) VALUES('" & Trim(firstNameTb.Text) & "', '" & Trim(lastNameTb.Text) & "', '" & Trim(DoBdtp.Text) & "', '" & Trim(customerMPNo.Text) & "', '" & Trim(emailTb.Text) & "', '" & Trim(addressRtb.Text) & "', '" & Trim(gender.text) & "', '" & Trim(ailmentstxtBx.Text) & "', '" & Trim(usernameTF.Text) & "', '" & Trim(passwordTF.Text) & "', '" & Trim(SecQuesBox.Text) & "', '" & Trim(SecAnsBox.Text) & "', '" & Trim(purpose.text) & "', '" & Trim(numWeight.Text) & " " & Trim(TypeOfWeight.Text) & "', '" & Trim(numHeight.Text) & " " & Trim(TypeOfHeight.Text) & "', '" & Trim(TotalBmi.Text) & "', '" & Trim(customer_weightLossGain.text) & "' , '" & trim(customer_weightUpdt) & "' "')"
                        check = cmdCusromer1.ExecuteReader.RecordsAffected()
                        If check > 0 Then
                            MsgBox("Data save successfully!", MsgBoxStyle.OkOnly, "Message :")
                            dsCustomer.AcceptChanges()
                        Else
                            MsgBox("Data FAILED to save!", MsgBoxStyle.OkOnly, "Message :")
                        End If

                        conn.Close()
                    End If
                Catch ex As Exception
                    MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Connection Error !!")
                End Try
            End If

please vote me if i already fix your problem, or ask me if you got problem.
Thank You!!

[Begginnerdev]

Change:

Dim dr As New DataRow = ds.Tables("tblRegCust").Rows.Add

To:

Dim dr As DataRow = ds.Tables("tblRegCust").Rows.Add

And change:

dat = Nothing

To:

da = Nothing

My appologies. This is what happens when you type code on the fly. :)

[Hazuan Nazri]

Aven.Seven,

Dont forget to mark this article as solve!! Thank you! hope we can work together again! ^_^