Ad:

VB.NET Discussion Thread
Unsolved
Views: 3335

Feb 4th, 2010

Saving datagrid data in SQL database

Expand Post »

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:

VB.NET Syntax (Toggle Plain Text)
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
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:

VB.NET Syntax (Toggle Plain Text)
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
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

Similar Threads

Upload txt data to MS SQL Database (Using C# and ASP.NET) in C#
Saving Drawing on PictureBox to SQL Database in VB.NET
exporting existing excel sheet data into sql database in VB.NET
retrieve data from SQL database into a combobox in Visual Basic 4 / 5 / 6
How to receive data from SQL database by ODBC class in C++

MCBambi

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Feb 2010

Permalink

Feb 5th, 2010

Re: Saving datagrid data in SQL database

>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.

Last edited by adatapost; Feb 5th, 2010 at 6:15 am.

adatapost

Moderator

Reputation Points: 2136

Solved Threads: 1228

Posting Genius

Offline

6,525 posts
since Oct 2008

Permalink

Feb 8th, 2010

-1

Re: Saving datagrid data in SQL database

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

VB.NET Syntax (Toggle Plain Text)
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
Module Module1
    Public strSQL As String
    Public MyConn As New SqlConnection("Server=QTMLC;Database=Test;UID=sa;PWD=sa")
End Module

VB.NET Syntax (Toggle Plain Text)
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
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

VB.NET Syntax (Toggle Plain Text)
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
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

Attached Files

Test.zip (341.9 KB, 158 views)

Last edited by gd2010; Feb 8th, 2010 at 1:57 am. Reason: edit code

gd2010

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Feb 2010

Permalink

Feb 8th, 2010

Re: Saving datagrid data in SQL database

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

adatapost

Moderator

Reputation Points: 2136

Solved Threads: 1228

Posting Genius

Offline

6,525 posts
since Oct 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in VB.NET Forum Timeline: radiobutton

Next Thread in VB.NET Forum Timeline: [OOD] Class design for with Database back end

DaniWeb Message

Cancel Changes

User Name
Password