1.11M Members

how to display the data in SQL SERVER using listbox in Visual Basic2010


*It's my first time here but the way I see things around here is good
I'm a newbie in VB2010 so please don't judge me too much...

HERE is my codes for INSERTING DATA but the problem is I don't know how to use it for SEARCHING

Imports System.Data.SqlClient

Public Class Form1
    Private Function INSERT() As String
        Dim con As New SqlConnection
        Dim reader As SqlDataReader
            con.ConnectionString = "Data Source=(local);Initial Catalog=OJT;Persist Security Info=True;User ID=sa;Password="

       Dim cmd As New SqlCommand("INSERT INTO dbo.ChkInOut(ControlNo,EmpNo,TaxiNo,PlateNo,Model,Make,CheckOutDate,CheckOutTime) VALUES('" + TxtCtrlNo.Text + "','" + txtEmpNo.Text + "','" + txtTaxiUnitNo.Text + "','" + txtPlateNo.Text + "','" + txtModel.Text + "','" + txtMake.Text + "','" + txtCheckOutDate.Text + "','" + txtCheckOutTime.Text + "') ", con)
                MsgBox("Inserting Information Success!", MsgBoxStyle.Information)
                reader = cmd.ExecuteReader()
            Catch ex As Exception
                MessageBox.Show("Error On Inserting Data to Database." & ex.Message)
            End Try
            Return "done"
        End Function

        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
            If TxtCtrlNo.Text <> "" And txtEmpNo.Text <> "" And txtCheckOutDate.Text <> "" And txtCheckOutTime.Text <> "" And txtTaxiUnitNo.Text <> "" And txtPlateNo.Text <> "" And txtModel.Text <> "" And txtMake.Text <> "" Then
                MsgBox("Unable to Insert Data! Check Fields!", vbOKOnly, "BASIC TAXI")
            End If
        End Sub
        Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        End Sub

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            txtCheckOutDate.Text = Format(Now, "short Date")
            Timer1.Enabled = True
            Timer1.Interval = 1
        End Sub

        Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
            txtCheckOutTime.Text = TimeOfDay
        End Sub

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        End Sub

End Class

I try to change the INSERT statement into UPDATE statement

'Dim cmd As New SqlCommand("UPDATE dbo.ChkInOut SET TaxiNo='" + txtMake.Text + "' WHERE ControlNo='12012996'", con)'

and it works fine, I see that I can use it as DELETE too but my problem is how to make it as SELECT statement?



The first thing you should do is use parameterized queries. A simple example of how to do that can be found here. As for doing a select, a basic select would look like

SELECT * FROM ChkInOut WHERE ControlNo = '12012996'

I'm assuming because you put single quotes around 12012996 that it is stored as a string rather than an int. As a parameterised query that would look like

cmd.CommandText = "SELECT * FROM ChkInOut WHERE ControlNo = @ctrlNo"

where txtCtrlNo is a text control containing the value to search for.

This article has been dead for over six months: Start a new discussion instead
Start New Discussion
View similar articles that have also been tagged: