Question Saving to DataBase. SQL

[elianeasmar]

Hello again
I have this code below. it won't give me any errors. but i can't see the records in SQL.

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
Dim strconnection As String = "Data Source=ELIANE-VAIO\SQLEXPRESS;Initial Catalog=SchoolDB;Integrated Security=True;"
Dim _cn As SqlConnection = New SqlConnection(strconnection)
_cn.Open()
Dim cmd As New SqlCommand
cmd.CommandText = "INSERT INTO StudentInfo(

, [Full-Name], [Position], [Title], [Phone-Number], [Address], [Date-Time]) VALUES (@Code, @Name, @Position, @Title, @PhoneNumber, @Address, @DateTime);"
        cmd.Parameters.AddWithValue("@Code", Form1.txtCode.Text)
        cmd.Parameters.AddWithValue("@Name", Form1.txtName.Text)
        cmd.Parameters.AddWithValue("@Position", Form1.cmbPosition.Text)
        cmd.Parameters.AddWithValue("@Title", Form1.cmbTitle.Text)
        cmd.Parameters.AddWithValue("@PhoneNumber", Form1.txtPhoneNumber.Text)
        cmd.Parameters.AddWithValue("@Address", Form1.txtAddress.Text)
        cmd.Parameters.AddWithValue("@DateTime", Form1.DateTimePicker1.Value)
        _cn.Close()
    End Sub

[/QUOTE]

Is there anything that has to be done in order to view the newly inserted record in SQL?

i can delete records with this. the record is deleted from SQL.

[CODE]Private Sub btnRemove_Click(sender As Object, e As EventArgs) Handles btnRemove.Click
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Try
            Dim strconnection As String = "Data Source=ELIANE-VAIO\SQLEXPRESS;Initial Catalog=SchoolDB;Integrated Security=True;"
            Dim _cn As SqlConnection = New SqlConnection(strconnection)
            _cn.Open()
            cmd.Connection = _cn
            cmd.CommandText = "Delete From studentInfo where code=1"
            cmd.Parameters.Add(New SqlParameter("@code", 99))
            cmd.ExecuteNonQuery()
            If MessageBox.Show("Do you really want to Delete this Record?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
                MsgBox("Operation Cancelled")
                Exit Sub
            End If
        Catch ex As Exception
            MessageBox.Show("Error while deleting record on table..." & ex.Message, "Delete Records")
        Finally
            con.Close()
        End Try
    End Sub

I appreciate your help
Thank you.


Lilly..

 

[IanRyder]

Hi,

You are not getting any errors because you are not executing that command against the Database. So, 3 errors that I can see:-

1) If you are running this code from Form1 then, as I said before, remove the reference to itself in each of your parameter declarations. Like I said, you do not need to do this but if you do want to do this then you can refer to the current Form by using the Keyword Me.

2) You have not added your Connection Object to the Command Object and therefore this will generate an error. You need to add:-

cmd.Connection = _cn

3) Finally, you need to execute the command against the Database using the ExecuteNonQuery method of the Command object. i.e:-

cmd.ExecuteNonQuery()

You actually get all this right in your Remove code so I am a bit surprised you struggled with the above. Anyway, just check your delete query. You have:-

cmd.CommandText = "Delete From studentInfo where code=1"

I hope that you realise that this is not using your Parameter?

Hope that helps.

Cheers,

Ian

BTW, that Delete Statement in the Remove Code should only be executed as part of an Else clause from your If statement.

 

[elianeasmar]

Done . Thank you Ian
I am going to post the code to help other people that are having problems like mine
THIS IS THE SAVE :
-------------------------

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        Try
            Dim strconnection As String = "Data Source=ELIANE-VAIO\SQLEXPRESS;Initial Catalog=SchoolDB;Integrated Security=True;"
            Dim _cn As SqlConnection = New SqlConnection(strconnection)
            Dim cmd As New SqlCommand
            _cn.Open()
            cmd.CommandText = "INSERT INTO StudentInfo([Code], [Full-Name], [Position], [Title], [Phone-Number], [Address], [Date-Time]) VALUES (@Code, @Name, @Position, @Title, @PhoneNumber, @Address, @DateTime);"
            cmd.Connection = _cn
            cmd.Parameters.AddWithValue("@Code", Val(Me.t1.Text))
            cmd.Parameters.AddWithValue("@Name", Me.t2.Text)
            cmd.Parameters.AddWithValue("@Position", Me.t3.Text)
            cmd.Parameters.AddWithValue("@Title", Me.t4.Text)
            cmd.Parameters.AddWithValue("@PhoneNumber", Me.t5.Text)
            cmd.Parameters.AddWithValue("@Address", Me.t6.Text)
            cmd.Parameters.AddWithValue("@DateTime", Me.t7.Value)
            cmd.ExecuteNonQuery()
            _cn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

AND THIS IS THE REMOVE :
-------------------------

Private Sub btnRemove_Click(sender As Object, e As EventArgs) Handles btnRemove.Click
        Me.StudentInfoBindingSource.RemoveCurrent()


        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Try
            Dim strconnection As String = "Data Source=ELIANE-VAIO\SQLEXPRESS;Initial Catalog=SchoolDB;Integrated Security=True;"
            Dim _cn As SqlConnection = New SqlConnection(strconnection)
            _cn.Open()
            cmd.Connection = _cn
            cmd.CommandText = "Delete From studentInfo where code= " & Val(t1.Text)
            'cmd.Parameters.Add(New SqlParameter("@code", 99))


            If MessageBox.Show("Do you really want to Delete this Record?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
                MsgBox("Operation Cancelled")
                Exit Sub
            Else
                cmd.ExecuteNonQuery()
            End If
        Catch ex As Exception
            MessageBox.Show("Error while deleting record on table..." & ex.Message, "Delete Records")
        Finally
            con.Close()
        End Try
    End Sub

Lilly..

 

[IanRyder]

Hi,

Good to hear. However, I am going to be a bit critical on you now:-

1) What's wrong with this code in the Remove button:-

cmd.CommandText = "Delete From studentInfo where code= " & Val(t1.Text)
'cmd.Parameters.Add(New SqlParameter("@code", 99))

Which principal have we been learning to adhere to that this code now violates?

2) Just a small comment but I would have also put all the code associated with the Delete Command in the Else statement and not just the ExecuteNonQuery method.

Hope that helps.

Cheers,

Ian

 

[elianeasmar]

I was trying something. But i forgot to remove the line.
Yes you are right. i put it in a an else statement
Thank you.

Lilly..

 

[elianeasmar]

Display data from SQL into a form

Hello
i need your help in something.
I have a DataBase. I need to search it, If found, i want to display the record into my textboxes and comboboxes.
This is my code. it works with no errors. But i don't know how to display the data from SQL into my form.

Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        If CheckBox1.Checked = True Then
            Dim strconnection As String = "Data Source=ELIANE-VAIO\SQLEXPRESS;Initial Catalog=SchoolDB;Integrated Security=True;"
            Dim _cn As SqlConnection = New SqlConnection(strconnection)
            Dim cmd As New SqlCommand
            Dim cmd1 As New SqlCommand
            _cn.Open()
            cmd.Connection = _cn
            Dim r As DataGridViewRow
            For Each r In Form1.DataGridView1.Rows
                cmd.CommandText = "select * from StudentInfo where Code = @t1; "
                cmd.ExecuteNonQuery()
            Next
            If MessageBox.Show("Record Found. Do you want to display the record in the textboxes?", "Found", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
                MsgBox("Display Cancelled")
                Exit Sub
            Else
                '    cmd.ExecuteNonQuery()
                MsgBox(":)")
            End If
        End If
End Sub

Thank you in advance

 

[IanRyder]

This is my code. it works with no errors. But i don't know how to display the data from SQL into my form.

Hi Lilly,

Sorry, but I do not believe that you have even Pressed your Search Button with CheckBox1 being Checked since you will most definitely get Runtime Errors with that code.

In truth, I would typically suggest a whole different approach for getting your Data from your Database and then creating a Search button but, in your case, I am going to stick with what you have for the moment until you understand why this code is completely wrong.

So, the first thing is to run the code that you have and identify the first Runtime error. Once you have fixed that, since you will already know how to fix this, you then need to read up on what the ExecuteNonQuery Method is used for.

SqlCommand.ExecuteNonQuery Method (System.Data.SqlClient)

Once you then understand where you went wrong you then need to look into using the ExecuteReader Method of the Command Object to returns rows from a Database:-

SqlCommand.ExecuteReader Method (System.Data.SqlClient)

The last thing I will mention is that there is absolutely no reason for a For Loop here.

If you still get stuck with creating this search button then by all means post back to the thread.

Cheers,

Ian

 

[elianeasmar]

Solved . 85%
I got another idea.
I thought mmaybe if i could load the data into a dtagridview. And search in the datagridview. When found: the row is selected.
And when the row is selected i double click it, and the data wil be loaded in my textboxes and comboboxes.
I did all that. but now i'm having a little problem
When i search the datagridview, let's say i have 2 names: "Eliane" and "Elie" . If i search for "Eli", it's getting a bit confusing.
I put the multiselection property of the datagridview to true. still a bit not gd.
I am working on solving it.
Thank you

 

[elianeasmar]

100% solved
Should i post the code?

 

[IanRyder]

Good For you and Well Done.

As to posting the code, that is completely up to you and if it works for you then maybe its not necessary. However, if you would like some advice by other programmers on maybe trying to do things more efficiently with you own code or getting some hints on maybe doing things a different way, then by all means post your code and ask that specific question.

Glad you got there in the end.

Cheers,

Ian

 

[elianeasmar]

Thank you
You helped me a lot
That is my code:

Private Sub t0_TextChanged(sender As Object, e As EventArgs) Handles t0.TextChanged
 If cbx.Text = "Code" Then
            If t0.Text <> "" Then
                Dim s As String = t0.Text
                DataGridView1.ClearSelection()
                Dim r As DataGridViewRow
                For Each r In DataGridView1.Rows
                    r.Selected = InStr(r.Cells(0).Value, t0.Text, CompareMethod.Text) > 0
                Next


                If DataGridView1.SelectedRows.Count = 0 Then
                    MsgBox("Not found!...Try again.")
                    t0.Clear()
                End If
  End If
        ElseIf cbx.Text = "Full-Name" Then
            If t0.Text <> "" Then
                If t0.Text <> "" Then
                    Dim s As String = t0.Text
                    DataGridView1.ClearSelection()
                    Dim r As DataGridViewRow
                    For Each r In DataGridView1.Rows
                        r.Selected = InStr(r.Cells(1).Value, t0.Text, CompareMethod.Text) > 0
                    Next
                    If DataGridView1.SelectedRows.Count = 0 Then
                        MsgBox("Not found!...Try again.")
                        t0.Clear()
                    End If
                End If
 End If
            ElseIf cbx.Text = "Position" Then
            If t0.Text <> "" Then
                If t0.Text <> "" Then
                    Dim s As String = t0.Text
                    DataGridView1.ClearSelection()
                    Dim r As DataGridViewRow
                    For Each r In DataGridView1.Rows
                        r.Selected = InStr(r.Cells(4).Value, t0.Text, CompareMethod.Text) > 0
                    Next
                    If DataGridView1.SelectedRows.Count = 0 Then
                        MsgBox("Not found!...Try again.")
                        t0.Clear()
                    End If
                End If
End If
            ElseIf cbx.Text = "Title" Then
                If t0.Text <> "" Then
                    Dim s As String = t0.Text
                    DataGridView1.ClearSelection()
                    Dim r As DataGridViewRow
                    For Each r In DataGridView1.Rows
                        r.Selected = InStr(r.Cells(5).Value, t0.Text, CompareMethod.Text) > 0
                    Next
                    If DataGridView1.SelectedRows.Count = 0 Then
                        MsgBox("Not found!...Try again.")
                        t0.Clear()
                    End If
End If
            ElseIf cbx.Text = "Phone-Number" Then
                If t0.Text <> "" Then
                    Dim s As String = t0.Text
                    DataGridView1.ClearSelection()
                    Dim r As DataGridViewRow
                    For Each r In DataGridView1.Rows
                        r.Selected = InStr(r.Cells(6).Value, t0.Text, CompareMethod.Text) > 0
                    Next
                    If DataGridView1.SelectedRows.Count = 0 Then
                        MsgBox("Not found!...Try again.")
                        t0.Clear()
                    End If
  End If
            ElseIf cbx.Text = "Address" Then
            If t0.Text <> "" Then
                If t0.Text <> "" Then
                    Dim s As String = t0.Text
                    DataGridView1.ClearSelection()
                    Dim r As DataGridViewRow
                    For Each r In DataGridView1.Rows
                        r.Selected = InStr(r.Cells(7).Value, t0.Text, CompareMethod.Text) > 0
                    Next
                    If DataGridView1.SelectedRows.Count = 0 Then
                        MsgBox("Not found!...Try again.")
                        t0.Clear()
                    End If
                End If
End If
            ElseIf cbx.Text = "Date-Time" Then
                If t0.Text <> "" Then
                    Dim s As String = t0.Text.Trim
                    DataGridView1.ClearSelection()
                    For x As Integer = 0 To DataGridView1.Rows.Count - 1
                        If CStr(DataGridView1.Rows(x).Cells(8).Value).StartsWith(s) Then
                            DataGridView1.FirstDisplayedScrollingRowIndex = x
                            DataGridView1.Item(8, x).Selected = True
                            Exit Sub
                        End If
                    Next
                    If t0.Text = "" Then
                        MsgBox("Not Found...Try again.")
                    End If
                End If
            End If
    End Sub

 

[IanRyder]

Hi,

I am definitely having a bad day today. What I should have said in my previous post was to make a New Thread to ask your New question since this has nothing to do with Saving to a Database.

Saying that, do you really need to Select Each Row of a DataGridView if the Specified Column name from a ComboBox matches something typed in a TextBox? On the basis that the first item found could be at the Start of the DataGridView and possibly another item maybe found at the End of a DataGridView then how would you ever know that these had actually been selected if the DataGridView had thousands of rows of Data??? The answer is you would not.

What you really need here is a Filter. However, to use a Filter you need to use a BindingSource which adds a whole new dimension of objects which you may not have yet been exposed to.

If you would like to explore using a Filter to limit what is displayed in a DataGridView then you need to start a New Thread to ask this question since this has nothing to do with saving data to your database. (I say that knowing that this will also actually change the way that you save your data to the database but lets take one step at a time)

By all means quote this post in your New Thread to help get things started but make sure you do your own research first. i.e:-

BindingSource.Filter Property (System.Windows.Forms)

Cheers,

Ian

 

[elianeasmar]

Sorry about that.
i kind of expected your reply. So i hided the non selected rows. and when i'm done with my search i show them again.
Big thank you