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?
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
Try this.. You need to use square brackets.
txtDisplayModel.Text = ds.Tables[0].Row[0]["Model "].ToStrin g()
txtDisplayVehicleRegistrat ion.Text = ds.Tables[0].Rows[0]["Vehi cleNumberP late"].ToS tring()
txtDisplayModel.Text = ds.Tables[0].Row[0]["Model
txtDisplayVehicleRegistrat
ASKER
Hi Pawan,
nope...
I get this error now:
Value of type 'System.Data.DataTableColl ection' cannot be converted to 'String'
nope...
I get this error now:
Value of type 'System.Data.DataTableColl
Can you pls post the entire code?
ASKER
This is the entire code, not much...I'll add the Class code also below
DatabaseAccess Class for SelectDataSet(strSQL)
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
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
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
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()
ASKER
Hi Pawan, thanks but I still get the same error
for ds.Tables[0]
Value of type 'System.Data.DataTableColl ection' cannot be converted to 'String'
for ds.Tables[0]
Value of type 'System.Data.DataTableColl
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
ASKER
thanks.
dont I need a conversion? any idea how we can explicitly convert in this VB code?
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?
The code you mentioned above is in VB.NET.
Do you want to convert this code in Vb6?
ASKER
Hi Pawan, tried your lastsuggestion (what was different?) it still gives the same error msg
Hi Prabhu - tried yours - same problem unfortuantely
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
txtDisplayVehicleRegistrat ion.Text = dsData.Tables[0].Rows[0]["Vehicl eNumberPla te"].ToStr ing()
txtDisplayModel.Text = dsData.Tables[0].Rows[0]["Model" ].ToString ()
End If
dsData = SelectDataSet(strSQL)
If dsData.Tables[0].Rows.Count > 0 Then
txtDisplayVehicleRegistrat
txtDisplayModel.Text = dsData.Tables[0].Rows[0]["Model"
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
ASKER
@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' "
>>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?
ASKER
@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
>>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!.
Please try my last code, it does not have any text like " ds.Tables[0]".
Thank you!.
ASKER
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?
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"))
ASKER
@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
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
ASKER
SOLVED!
so your last code works, with parenthesis and not square brackets
thanks so much.....2 days of blood sweat ends.....thank you all
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 !..
ASKER
@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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Pawan!
Welcome !!
ASKER
Open in new window