code for Display database record according to ComboBox selection to text box in vb 2008

Question

1.00/5 (2 votes)

See more:

I have sql server 2005 .there i have 1 table name data which has slno,name,type,shape as column fields
i want to display the records of name,type,shape in textbox on combobox selection of slno

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

'Dim strcon As String = ("Data Source=.\INSTANCE;initial catalog=record;user=sa;password=gariahat")
Dim cn As New SqlConnection("Data Source=.\INSTANCE;initial catalog=record;user=sa;password=gariahat")
'Dim da As New SqlDataAdapter
Dim cmd As New SqlCommand
Dim ds As New DataSet
Dim dt As DataTable

'Try

cmd.Connection = cn
cn.Open()

Dim da As New SqlDataAdapter("select * from data", cn)
dt = New DataTable

ds = New DataSet
da.Fill(ds, "data")

For i As Integer = 0 To ds.Tables("data").Rows.Count - 1
If ComboBox1.SelectedItem = ds.Tables("data").Rows(i).Item("lot_no").ToString() Then
TextBox3.Text = ds.Tables("data").Rows(i + 1).Item("type").ToString()
TextBox4.Text = ds.Tables("data").Rows(i + 2).Item("shape").ToString()
TextBox5.Text = ds.Tables("data").Rows(i + 3).Item("size").ToString()
TextBox6.Text = ds.Tables("data").Rows(i + 4).Item("place").ToString()
TextBox7.Text = ds.Tables("data").Rows(i + 5).Item("weight").ToString()

End If

Next

this is the above code, bt showing error in for loop statement as null reference

Posted 3-Jul-13 22:39pm

sudeshna from bangkok

Updated 13-Aug-17 22:25pm

Add a Solution

4 solutions

Solution 3

Because of i+1, i+2, i+3... is the problem.

Just remove that +1, +2 +3... then it will work.

Posted 3-Jul-13 23:14pm

munnalingam

Comments

sudeshna from bangkok 4-Jul-13 5:50am

thanks a lot,
its working now.

I have another query that on same form i have 2 other fields name and date

after the textbox gets filled on selection of combo box, i will manually enter name and date in the other two tet box and this data on clicking submit button will get stored in another table name sort.

previously my table name was data from where i was fetching data and storing in textbox based on combo box selection.

now in that same form, i want two other records to be added in textboxs which is column and date and will be updated in database table sort.

what code will i write?

please help.

thanks in advance
sudeshna

sudeshna from bangkok 4-Jul-13 5:51am

this is the query i wrote, but showing error

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cn As New SqlConnection("Data Source=.\INSTANCE;initial catalog=record;user=sa;password=gariahat")
Dim da As New SqlDataAdapter
Dim cmd As New SqlCommand
Dim ds As New DataSet
'Dim dr As SqlDataReader
cmd.Connection = cn
cn.Open()
cmd.CommandText = "insert into sort(name,issue_dt) values (' " & TextBox2.Text & " ','" & TextBox8.Text & " ')"
cmd.ExecuteNonQuery()
MsgBox("Data successfully added", MsgBoxStyle.Information)
' Me.SortTableAdapter.Insert(ComboBox1.Text, TextBox6.Text)
Me.SortTableAdapter.Fill(Me.RecordDataSet.sort)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

thanh_bkhn · Accepted Answer · 2013-07-03T23:11:00

Solution 1

I saw you access different rows in each setting statement. A row can be access by Rows(i) as below

VB

TextBox3.Text = ds.Tables("data").Rows(i).Item("type").ToString()
TextBox4.Text = ds.Tables("data").Rows(i).Item("shape").ToString()
TextBox5.Text = ds.Tables("data").Rows(i).Item("size").ToString()
TextBox6.Text = ds.Tables("data").Rows(i).Item("place").ToString()
TextBox7.Text = ds.Tables("data").Rows(i).Item("weight").ToString()

Posted 3-Jul-13 23:11pm

thanh_bkhn

Comments

sudeshna from bangkok 4-Jul-13 5:50am

thanks a lot,
its working now.

I have another query that on same form i have 2 other fields name and date

after the textbox gets filled on selection of combo box, i will manually enter name and date in the other two tet box and this data on clicking submit button will get stored in another table name sort.

previously my table name was data from where i was fetching data and storing in textbox based on combo box selection.

now in that same form, i want two other records to be added in textboxs which is column and date and will be updated in database table sort.

what code will i write?

please help.

thanks in advance
sudeshna

sudeshna from bangkok 4-Jul-13 5:52am

this is the query i wrote bt showing error that lot_no cannot be null

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cn As New SqlConnection("Data Source=.\INSTANCE;initial catalog=record;user=sa;password=gariahat")
Dim da As New SqlDataAdapter
Dim cmd As New SqlCommand
Dim ds As New DataSet
'Dim dr As SqlDataReader
cmd.Connection = cn
cn.Open()
cmd.CommandText = "insert into sort(name,issue_dt) values (' " & TextBox2.Text & " ','" & TextBox8.Text & " ')"
cmd.ExecuteNonQuery()
MsgBox("Data successfully added", MsgBoxStyle.Information)
' Me.SortTableAdapter.Insert(ComboBox1.Text, TextBox6.Text)
Me.SortTableAdapter.Fill(Me.RecordDataSet.sort)

thanh_bkhn 4-Jul-13 6:14am

If you want the new values to be updated into the database, you must use Update command.
Try this:
cmd.CommandText = "Update Sort set " &
"type = '" & TextBox3.Text & "', " &
(shape, size, place, weight is similar to type)
"name = '" & TextBox2.Text & "', " &
"issue_dt = '" & TextBox8.Text & "'" &
"Where lot_no = '" & ComboBox1.SelectedItem & "'"

sudeshna from bangkok 4-Jul-13 6:46am

after update sort set how to write the syntax? i did not understand. the quotation part
Can you please post again.

thanks

thanh_bkhn 4-Jul-13 6:54am

Don't you understand the Update syntax? So you could take a look at http://msdn.microsoft.com/en-us/library/8hwekas8(v=vs.80).aspx
The essential thing to keep in mind is, the update command requires a Where clause, in your case it is the lot_no is the previously selected combobox's value, otherwise you will update entire the database.

sudeshna from bangkok 4-Jul-13 6:58am

i understood the update command but the punctuation part,single qquotes etc that how u have placed i cant understand
"type = '" & TextBox3.Text & "', " & (shape, size, place, weight is similar to type)
and this parta also. in bracket what will i put?

thanh_bkhn 4-Jul-13 7:04am

(shape, size, place, weight is similar to type) -> I mean these element should be coded similarly to the "type" item, such as
"shape = '" & TextBox4.Text & "', " &
"size = '" & TextBox5.Text & "', " &
and so on

sudeshna from bangkok 4-Jul-13 22:57pm

i wrote the code
Dim cn As New SqlConnection("Data Source=.\INSTANCE;initial catalog=record;user=sa;password=gariahat")
Dim da As New SqlDataAdapter
Dim cmd As New SqlCommand
Dim ds As New DataSet
'Dim dr As SqlDataReader
cmd.Connection = cn
cn.Open()
cmd.CommandText = cmd.CommandText = "Update Sort set " & "type = '" & TextBox3.Text & "', " & "shape='" & TextBox4.Text & " '," & "size='" & TextBox5.Text & " '," & "place='" & TextBox6.Text & " ' ," & "weight='" & TextBox7.Text & " '," & "name = '" & TextBox2.Text & "', " & "issue_dt = '" & TextBox8.Text & "'" & "Where lot_no = '" & ComboBox1.SelectedItem & "'"
cmd.ExecuteNonQuery()
MsgBox("Data successfully updated", MsgBoxStyle.Information)
Me.SortTableAdapter.Insert(ComboBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text, TextBox7.Text, TextBox8.Text)
Me.SortTableAdapter.Fill(Me.RecordDataSet.sort)

but showing error in that Could not find stored procedure 'False'.

sudeshna from bangkok 4-Jul-13 23:02pm

sorry i wrote cmd.command text twice by mistake, so was showing error.
but now msgbox showing that data successfully updated but its nt updating in database sort, its showing error in the below line
Me.SortTableAdapter.Insert(ComboBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text, TextBox7.Text, TextBox8.Text)

the error showing is

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ds". The conflict occurred in database "52B102E3BD1B9575679C2B8ECC6F7436_UMENTS\VISUAL STUDIO 2008\PROJECTS\WINDOWSAPPLICATION1\WINDOWSAPPLICATION1\BIN\DEBUG\RECORD.MDF", table "dbo.data", column 'lot_no'. The statement has been terminated.

thanh_bkhn 4-Jul-13 23:27pm

When you encounter an error, the entire SQL statement will be roll backed, includes the Update command.
So your error is in the Insert statement. You should check the relation of sort table with the table in which you're inserting.

sudeshna from bangkok 5-Jul-13 4:08am

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cn As New SqlConnection("Data Source=.\INSTANCE;initial catalog=record;user=sa;password=gariahat")
Dim da As New SqlDataAdapter
Dim cmd As New SqlCommand
Dim ds As New DataSet
'Dim dr As SqlDataReader
cmd.Connection = cn
cn.Open()
cmd.CommandText = "insert into data(lot_no,type,shape,size,place,weight) values (' " & TextBox1.Text & " ',' " & TextBox2.Text & " ',' " & TextBox3.Text & " ',' " & TextBox4.Text & " ',' " & TextBox5.Text & " ',' " & TextBox6.Text & " ')"
cmd.ExecuteNonQuery()
MsgBox("Data successfully added", MsgBoxStyle.Information)
gencatid()
Me.DataTableAdapter.Insert(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text)
Me.DataTableAdapter.Fill(Me.RecordDataSet.data)
Me.TextBox1.Text = ""
Me.TextBox2.Text = ""
Me.TextBox3.Text = ""
Me.TextBox4.Text = ""
Me.TextBox5.Text = ""
Me.TextBox6.Text = ""

this is the insert command that i have written in table data where lot_no is primary key and in sort table lot_no is foreign key

sudeshna from bangkok 5-Jul-13 4:11am

i am initially inserting values in table data from that i am retrieving all the values and storing in sort table with 2 new coulumns being added.

sudeshna from bangkok 5-Jul-13 4:17am

Sir, please help me. otherwise i m stuck.

i will be greatful to you.
thanks in advance
sudeshna

thanh_bkhn 5-Jul-13 4:22am

Currently what is your error message?

sudeshna from bangkok 5-Jul-13 6:09am

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ds". The conflict occurred in database "52B102E3BD1B9575679C2B8ECC6F7436_UMENTS\VISUAL STUDIO 2008\PROJECTS\WINDOWSAPPLICATION1\WINDOWSAPPLICATION1\BIN\DEBUG\RECORD.MDF", table "dbo.data", column 'lot_no'. The statement has been terminated.

this is the error message

sudeshna from bangkok 5-Jul-13 6:25am

i have removed the foreign key relationship with table data and sort, but still showing error

thanh_bkhn 5-Jul-13 6:29am

Yes the problem is with your relationship, but I don't understand why it still error after you remove it?

sudeshna from bangkok 5-Jul-13 6:59am

i dnt know sir.

and another thing sir i have two other tables factory and facsort. these two tables also i had done same like data and sort table. only thing when i created the factory and facsort table, i did not create fk. so when i am updating the facsort table taking values from factory table, no error showing,but my facsort table is not getting updated in database

where as data and sort table error showing

sudeshna from bangkok 5-Jul-13 9:25am

please sir,can you help me.my project is stucked here. i am not being able to continue unless this data gets stored in sort table and also in facsort table

thanh_bkhn 5-Jul-13 10:43am

When there is no error, so there is a possibility that you forgot to call Commit

sudeshna from bangkok 5-Jul-13 11:57am

where will i call commit? and sir in my facsort table already no data is there,all fields are null. so how will the update statement work?

sudeshna from bangkok 5-Jul-13 11:59am

can you give me the code in vb.net to insert data in two different tables using transaction.
suppose i use insert statement in 1 table,it will automatically get inserted in 2nd table also with same column name and values

bhuvamehul · Accepted Answer · 2013-07-04T02:33:00

Try this way

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

'Dim strcon As String = ("Data Source=.\INSTANCE;initial catalog=record;user=sa;password=gariahat")
Dim cn As New SqlConnection("Data Source=.\INSTANCE;initial catalog=record;user=sa;password=gariahat")
'Dim da As New SqlDataAdapter
Dim cmd As New SqlCommand
Dim ds As New DataSet
Dim dt As DataTable

'Try

cmd.Connection = cn
cn.Open()

Dim da As New SqlDataAdapter("select * from data where lot_no=" & cint(ComboBox1.SelectedItem), cn)
dt = New DataTable

ds = New DataSet
da.Fill(ds, "data")

TextBox3.Text = ds.Tables("data").Rows(0).Item("type").ToString()
TextBox4.Text = ds.Tables("data").Rows(0).Item("shape").ToString()
TextBox5.Text = ds.Tables("data").Rows(0).Item("size").ToString()
TextBox6.Text = ds.Tables("data").Rows(0).Item("place").ToString()
TextBox7.Text = ds.Tables("data").Rows(0).Item("weight").ToString()

munnalingam · Accepted Answer · 2013-07-03T23:14:00

Solution 2

Because of i+1, i+2, i+3... is the problem. You are referring a row which is not available.

Just remove that +1, +2 +3... then it will work.

Posted 3-Jul-13 23:14pm

munnalingam

Updated 3-Jul-13 23:16pm

v2

Comments

sudeshna from bangkok 5-Jul-13 4:12am

I have another query that on same form i have 2 other fields name and date after the textbox gets filled on selection of combo box, i will manually enter name and date in the other two tet box and this data on clicking submit button will get stored in another table name sort. previously my table name was data from where i was fetching data and storing in textbox based on combo box selection. now in that same form, i want two other records to be added in textboxs which is column and date and will be updated in database table sort. what code will i write? please help. thanks in advance sudeshna

sudeshna from bangkok 5-Jul-13 4:14am

i wrote the code
Dim cn As New SqlConnection("Data Source=.\INSTANCE;initial catalog=record;user=sa;password=gariahat") Dim da As New SqlDataAdapter Dim cmd As New SqlCommand Dim ds As New DataSet 'Dim dr As SqlDataReader cmd.Connection = cn cn.Open() cmd.CommandText = cmd.CommandText = "Update Sort set " & "type = '" & TextBox3.Text & "', " & "shape='" & TextBox4.Text & " '," & "size='" & TextBox5.Text & " '," & "place='" & TextBox6.Text & " ' ," & "weight='" & TextBox7.Text & " '," & "name = '" & TextBox2.Text & "', " & "issue_dt = '" & TextBox8.Text & "'" & "Where lot_no = '" & ComboBox1.SelectedItem & "'" cmd.ExecuteNonQuery() MsgBox("Data successfully updated", MsgBoxStyle.Information) Me.SortTableAdapter.Insert(ComboBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text, TextBox7.Text, TextBox8.Text) Me.SortTableAdapter.Fill(Me.RecordDataSet.sort) but showing error in that Could not find stored procedure 'False'.