Saving datagrid data in SQL database

[MCBambi]

hie if you could help me out i'll be forever grateful!!

okay i am creating an ordering system but when i try to add a new customer it allos me to add but it doesnt save to the database and i can not also call it up the code i have is:

Private Sub frmAddCustomer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' Purpose: Add new Customer record and assign its default values
        ' Clear the tables in the DataSet ready for a new record to be added
        'dtasetAmway.Tables("OrderDeatils").Clear()
        dtasetAmway.Tables("Customer").Clear()
        ' Add a new record to Customer table
        dtasetAmway.Tables("Customer").Rows.Add(Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)
        ' Make this the current row
        currentrowno = 0
        ' Update the Customer table in the database so the CustomerNo gets allocated by SQL Server
        dtaadpCustomer.Update(dtasetAmway, "Customer")
        ' Retrieve the record just added to the Customer table in the database so user can see CustomerNo
        ' Create query to retrieve required record: the record with highest CustomerNo
        dtaadpCustomer.SelectCommand.CommandText = "SELECT TOP 1 * FROM Customer ORDER BY CustomerNo DESC"
        ' Empty the Customer table in the DataSet so that the Customer record can be retrieved from the database
        dtasetAmway.Tables("Customer").Clear()
        ' Execute the SqlCommand to fill a table called Customer in the DataSet
        dtaadpCustomer.Fill(dtasetAmway, "Customer")
        ' Make a note that user is adding a record
        AddRecord = True
        'View CustomerNo in textbox
        txtCustomerNo.Text = dtasetAmway.Tables("Customer").Rows(currentrowno).Item("CustomerNo")
        'Make txtCustomer Read Only so that it can not be changed
        txtCustomerNo.ReadOnly = True
    End Sub

Save:

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        ' Purpose: Save the changes made to the Customer record on the screen
        ' Check if compulsory fields don't contain data, show error message and place cursor back in field
        If txtFirstName.Text = "" Then
            MessageBox.Show("First Name must be filled in.", "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtFirstName.Focus()
        ElseIf txtSurname.Text = "" Then
            MessageBox.Show("Surname must be filled in.", "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtSurname.Focus()
        ElseIf txtDOB.Text = "" Then
            MessageBox.Show("D.O.B. must be filled in.", "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtDOB.Focus()
        ElseIf txtAddress.Text = "" Then
            MessageBox.Show("Address must be filled in.", "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtAddress.Focus()
        ElseIf txtTelephone.Text = "" Then
            MessageBox.Show("Telephone must be filled in.", "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtTelephone.Focus()
      
        Else
            ' Purpose: Save the changes made to the Customer record on the screen
            ' Copy the data from the screen into the DataSet
            dtasetAmway.Tables("Customer").Rows(currentrowno)("FirstName") = txtFirstName.Text
            dtasetAmway.Tables("Customer").Rows(currentrowno)("Surname") = txtSurname.Text
            dtasetAmway.Tables("Customer").Rows(currentrowno)("DOB") = txtDOB.Text
            dtasetAmway.Tables("Customer").Rows(currentrowno)("Address") = txtAddress.Text
            dtasetAmway.Tables("Customer").Rows(currentrowno)("TelephoneNo") = txtTelephone.Text
            ' Update the Account table in the database
            dtaadpCustomer.Update(dtasetAmway, "Customer")
            MessageBox.Show("The updates have been saved to disk.", "Save Updates", MessageBoxButtons.OK, MessageBoxIcon.Information)
            'close form and open Menu
            Me.Hide()
            frmMenu.Show()
        End If
    End Sub

thanks in advance

[kvprajapati]

>but it doesnt save to the database...
Please post connectionString (do not add username/password) and exception trace.
PS: Please check the database file located at Bin folder of application.

[gd2010]

This is code add data in to SQL 2000 using VB.NET 2008
Database:
Table: tbl_users
id char(5) Primary key
Acc nvarchar(32)
Pass nvarchar(32)
Email nvarchar(200)
Module

Imports System.Data.SqlClient
Module Module1
    Public strSQL As String
    Public MyConn As New SqlConnection("Server=QTMLC;Database=Test;UID=sa;PWD=sa")
End Module

Imports System.Data.SqlClient
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Try
            If txtId.Text = "" Or txtAcc.Text = "" Or txtPass.Text = "" Or txtEmail = "" Then
                MsgBox("You must enter fully in to textbox", MsgBoxStyle.OkOnly, "Error")
                Return
            End If
            strSQL = "Insert into tbl_users (Id, Acc, Pass, email)"
            strSQL = strSQL + " values('" & UCase(txtId.Text) & "','" & txtAcc.Text & "','" & txtPass.Text & "','" & txtEmail.Text & "')"
            MyConn.Open()
            Dim cmd As New SqlCommand(strSQL, MyConn)
            cmd.ExecuteNonQuery()
            MyConn.Close()
            strSQL = Nothing
        Catch ex As Exception
            MyConn.Close()
            MsgBox("Account: " & txtAcc.Text & "is registed!")
        End Try
        ShowData()
        End Sub

Public Sub ShowData()
        Try
            strSQL = "SELECT id, Acc, Pass, Email FROM tbl_users"
            MyConn.Open()
            Dim Myda As New SqlDataAdapter(strSQL, MyConn)
            Dim Mydt As New DataTable
            Myda.Fill(Mydt)
            Me.DataGridView1.DataSource = Mydt
            KT_DN = True
            strSQL = Nothing
            MyConn.Close()
        Catch ex As Exception
            MyConn.Close()
            MsgBox("Disconnect from server")
        End Try
    End Sub

File attach Pass: www.gd210.com

[kvprajapati]

To help protect against SQL statement exploits, never create SQL queries using string concatenation. Instead, use a parameterized query and assign user input to parameter objects.

Read - ADO.NET Security Best Practices