Link to home
Start Free TrialLog in
Avatar of fadiel ras
fadiel ras

asked on

How to Retrieve data from SQl database into multiple Textboxes VB.net

I had this question after viewing How to retrieve data from sql database into textbox - vb.net.

I'm battling to populate my search results to multiple textboxes for that row in my data set.
I'be to populate 1 cell, based on my search: VehicleNumberPlate which works correctly
How do I fill each textbox with the matching cell from my Sql table?

strSQL = " SELECT DISTINCT VehicleNumberPlate, Model"
        strSQL = strSQL & " From tblVehicle "
        strSQL = strSQL & " Where VehicleNumberPlate > 0 "
        If txtSearchVehicleReg.Text <> "" Then
            strSQL = strSQL & " And VehicleNumberPlate = '" & Trim(UCase(Replace(txtSearchVehicleReg.Text, "'", ""))) & "'"
        End If

        dsData = SelectDataSet(strSQL)

        txtDisplayVehicleRegistration.Text = txtSearchVehicleReg.Text

Open in new window

Avatar of fadiel ras
fadiel ras

ASKER

I tried the code below from the EE example, but I get an error: Cannot find Table (0)

        If ds.Tables(0).Rows.Count > 0 Then
            txtDisplayVehicleRegistration.Text = ds.Tables(0).Rows(0)("VehicleNumberPlate").ToString()
            txtDisplayModel.Text = ds.Tables(0).Rows(0)("Model").ToString()
        End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

Open in new window

Try this.. You need to use square brackets.

txtDisplayModel.Text = ds.Tables[0].Row[0]["Model"].ToString()

txtDisplayVehicleRegistration.Text = ds.Tables[0].Rows[0]["VehicleNumberPlate"].ToString()
Hi Pawan,
nope...
I get this error now:
Value of type 'System.Data.DataTableCollection' cannot be converted to 'String'
Can you pls post the entire code?
This is the entire code, not much...I'll add the Class code also below

Imports Claims.General
Imports Claims.DatabaseAccess
Imports System.Data
Imports System.Configuration

Public Class frmCaptureNewIncidentVV02
    Public dsCombos As New DataSet
    Public ds As New DataSet
    Public blnUpdate As Boolean

    Private Sub btnSearchVehicleReg_Click(sender As Object, e As EventArgs) Handles btnSearchVehicleReg.Click
        Dim strSQL As String = ""
        Dim dsData As DataSet
        Dim dsReturn As New DataSet
        Dim dt As DataTable

        Try
            strSQL = " SELECT DISTINCT VehicleNumberPlate, Model"
            strSQL = strSQL & " From tblVehicle "
            strSQL = strSQL & " Where VehicleNumberPlate > 0 "
            If txtSearchVehicleReg.Text <> "" Then
                strSQL = strSQL & " And VehicleNumberPlate = '" & Trim(UCase(Replace(txtSearchVehicleReg.Text, "'", ""))) & "'"
            End If

            dsData = SelectDataSet(strSQL)

            If ds.Tables(0).Rows.Count > 0 Then
                txtDisplayVehicleRegistration.Text = ds.Tables(0).Rows(0)("VehicleNumberPlate").ToString()
                txtDisplayModel.Text = ds.Tables(0).Rows(0)("Model").ToString()
            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Sub
End Class

Open in new window


DatabaseAccess Class for SelectDataSet(strSQL)
   
 Public Shared Function SelectDataSetSP(ByVal strSql As String) As DataSet

        Dim dsReturn As New DataSet

        cmSelect.CommandText = strSql
        cmSelect.Connection = sqlCon
        cmSelect.CommandType = CommandType.StoredProcedure
        sqlCon.Close()
        sqlCon.Open()
        Dim adapter As New SqlDataAdapter()
        adapter.SelectCommand = cmSelect
        adapter.Fill(dsReturn)
        sqlCon.Close()

        ClearParm()
        Return dsReturn

    End Function

Open in new window

try..

Imports Claims.General
Imports Claims.DatabaseAccess
Imports System.Data
Imports System.Configuration

Public Class frmCaptureNewIncidentVV02
    Public dsCombos As New DataSet
    Public ds As New DataSet
    Public blnUpdate As Boolean

    Private Sub btnSearchVehicleReg_Click(sender As Object, e As EventArgs) Handles btnSearchVehicleReg.Click
        Dim strSQL As String = ""
        Dim dsData As DataSet
        Dim dsReturn As New DataSet
        Dim dt As DataTable

        Try
            strSQL = " SELECT DISTINCT VehicleNumberPlate, Model"
            strSQL = strSQL & " From tblVehicle "
            strSQL = strSQL & " Where VehicleNumberPlate > 0 "
            If txtSearchVehicleReg.Text <> "" Then
                strSQL = strSQL & " And VehicleNumberPlate = '" & Trim(UCase(Replace(txtSearchVehicleReg.Text, "'", ""))) & "'"
            End If

            dsData = SelectDataSet(strSQL)

            If ds.Tables[0].Rows.Count > 0 Then
                txtDisplayVehicleRegistration.Text = ds.Tables[0].Rows[0]["VehicleNumberPlate"].ToString()
                txtDisplayModel.Text = ds.Tables[0].Rows[0]["Model"].ToString()
            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Sub
End Class

Open in new window

There is no reason why your code would fail except the reason Pawan had stated. Along with that, you can also try the below one to speed of the look up when the fields grow in #.

txtDisplayVehicleRegistration.Text = ds.Tables[0].Rows[0][0].ToString()
txtDisplayModel.Text = ds.Tables[0].Rows[0][1].ToString()

Open in new window

Hi Pawan, thanks but I still get the same error

for ds.Tables[0]

Value of type 'System.Data.DataTableCollection' cannot be converted to 'String'
Use this ..

dsData = SelectDataSet(strSQL)

            If dsData.Tables[0].Rows.Count > 0 Then
                txtDisplayVehicleRegistration.Text = dsData.Tables[0].Rows[0]["VehicleNumberPlate"].ToString()
                txtDisplayModel.Text = dsData.Tables[0].Rows[0]["Model"].ToString()
            End If

Open in new window

thanks.
dont I need a conversion? any idea how we can explicitly convert in this VB code?
Is that worked ?

The code you mentioned above is in VB.NET.

Do you want to convert this code in Vb6?
Hi Pawan, tried your lastsuggestion (what was different?) it still gives the same error msg

Hi Prabhu - tried yours  - same problem unfortuantely
Changed below- , Which line is giving you the error?

dsData = SelectDataSet(strSQL)

            If dsData.Tables[0].Rows.Count > 0 Then
                txtDisplayVehicleRegistration.Text = dsData.Tables[0].Rows[0]["VehicleNumberPlate"].ToString()
                txtDisplayModel.Text = dsData.Tables[0].Rows[0]["Model"].ToString()
            End If


Imports Claims.General
Imports Claims.DatabaseAccess
Imports System.Data
Imports System.Configuration

Public Class frmCaptureNewIncidentVV02
    Public dsCombos As New DataSet    
    Public blnUpdate As Boolean

    Private Sub btnSearchVehicleReg_Click(sender As Object, e As EventArgs) Handles btnSearchVehicleReg.Click
        Dim strSQL As String = ""
        Dim dsData As DataSet
        Dim dsReturn As New DataSet
        Dim dt As DataTable

        Try
            strSQL = " SELECT DISTINCT VehicleNumberPlate, Model"
            strSQL = strSQL & " From tblVehicle "
            strSQL = strSQL & " Where VehicleNumberPlate > 0 "
            If txtSearchVehicleReg.Text <> "" Then
                strSQL = strSQL & " And VehicleNumberPlate = '" & Trim(UCase(Replace(txtSearchVehicleReg.Text, "'", ""))) & "'"
            End If

            dsData = SelectDataSet(strSQL)

            If dsData.Tables[0].Rows.Count > 0 Then
                txtDisplayVehicleRegistration.Text = dsData.Tables[0].Rows[0]["VehicleNumberPlate"].ToString()
                txtDisplayModel.Text = dsData.Tables[0].Rows[0]["Model"].ToString()
            End If
			
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Sub
End Class

Open in new window

@Pawan
>>The code you mentioned above is in VB.NET. - yes that's correct

>>Do you want to convert this code in Vb6? - I mean convert that textbox Or something?....not actually sure, seeing that error
"...cannot be converted to 'String' "
Can you try with Convert.ToString() method?
@Pawan
>>Which line is giving you the error?
every line that contains : " ds.Tables[0] "
so its lines 26,27,28 from your last code posted
Hi,

Please try my last code, it does not have any text like " ds.Tables[0]".

Thank you!.
Hi
I tried this code from another sample project I have, but still no luck, as "Conversion" is not declared error and also I've never used this.
does it make sense to you?

txtDisplayVehicleRegistration.Text = Conversions.ToString(Me.ds.Tables.Item(1).Rows.Item(0).Item("VehicleNumberPlate"))
txtDisplayModel.Text = Conversions.ToString(Me.ds.Tables.Item(1).Rows.Item(0).Item("Model"))

Open in new window

@Pawan
apologies - it also does it for dsdata, I tried your last code
updated:
>>Which line is giving you the error?
every line that contains : " dsData.Tables[0] "
so its lines 26,27,28 from your last code posted
SOLVED!
so your last code works, with parenthesis and not square brackets
thanks so much.....2 days of blood sweat ends.....thank you all
Great !! Enjoy !..
@Pawan,
do you re-post with () then I accept that as the best solution? as you did solve it for me...so I should credit you accordingly
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you Pawan!
Welcome !!