VB.NET

[naazsayed]

i want a field to be fetched from the database of one form and display it in another form

i tried but the simple program works that to the text in the textbox appears in another forms textbox on button click event

how to retrieve a value from a database and display it in textbox or comboboxes???

can anyone help?????

[hericles]

Post up what you've done so far and we'll probably be able to help you out

[naazsayed]

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'private Form2 otherForm = new Form2();

'TextBox1.Text = otherform.TextBox1.Text

Dim Obj As New Form2
Obj.PassedText = TextBox1.Text
Obj.Show()
End Sub

and in form2 i had set txtbox modifier property to public

Private _passedText As String

Public Property [PassedText]() As String
Get
Return _passedText
End Get
Set(ByVal Value As String)
_passedText = Value
End Set
End Property

but this was the simple program i want database value to be fetched

i tried that by taking combobox using databounds but it just shows data available in gridview on searching

[lolafuertes]

In order to retrieve data from a database you'll need:

a) Open a connectino to the database
b) Create a SELECT command from the table, filtering the result if needed.
The result of this command can be retieved using 2 ways
* Fill a table in a dataset using a data adapter, then use this table to be the source for your combobox, and refresh the combobox contents
* Create a datareader, clear all items in the combobox, then for each record read by the datareader, add the relevant info to a new combobox item, and finally close the datareader.

As you already experienced using properties, you also can create public subsor functions on the destination forms that do this work. Then calling them from the first form, and passing the appropiate values for filtering, will do the tric.

Hope this helps

[naazsayed]

i tried dat to using combobox databounded it but not working

[lolafuertes]

Sorry, this is not shown on your post.

[naazsayed]

ok,my mistake sorry

i also want if the entry already exist it must show error that it exists what to do?

[lolafuertes]

Please, be so kind to post your code here so we can see what the problem is with your databound combobox.

[naazsayed]

tried dis but not working

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

'Dim strConn As String = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()
Dim strConn As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Nazneen\Master\Master\Masterdb.mdf;Integrated Security=True;User Instance=True"
Dim conn As New SqlConnection(strConn)

Dim da As New SqlDataAdapter("select * from SchemeKitMaster", conn)

Dim ds As New DataSet()

da.Fill(ds, "SchemeKitMaster")

' Filling a employee table

Dim dt As DataTable = ds.Tables("SchemeKitMaster")

dt = RemoveDuplicateRows(dt, "month")

DataGridView1.DataSource = ds.Tables("SchemeKitMaster").DefaultView
'DataGridView1.DataBindings("month")

'DataGridView.DataSource = ds.Tables("SchemeKitMaster").DefaultView

'DataGridView.DataBind()

End Sub

Public Function RemoveDuplicateRows(ByVal dTable As DataTable, ByVal colName As String) As DataTable

Dim hTable As New Hashtable()

Dim duplicateList As New ArrayList()

'Add list of all the unique item value to hashtable, which stores combination of key, value pair.

'And add duplicate item value in arraylist.

For Each drow__1 As DataRow In dTable.Rows

If hTable.Contains(drow__1(colName)) Then

duplicateList.Add(drow__1)

Else

hTable.Add(drow__1(colName), String.Empty)

End If

Next

'Removing a list of duplicate items from datatable.

For Each dRow__2 As DataRow In duplicateList

dTable.Rows.Remove(dRow__2)

Next

'Datatable which contains unique records will be return as output.

Return dTable

End Function
'End Sub

[lolafuertes]

Sorry but I want to understand properly the problem. Lets consider the scenarios again.

A) You have a from1. This form1 has a textBox1 and when some one clicks on the button1 you want to create a form2, set the PassedText and show the result.
If I understood well, this is working fine for you.

B) You have the same form1, but now, when someone clicks on the button3 you want
* create a form3 having a DataGridView,
* pass a text to search in the database,
* fill a datatable with the relevant records found,
* remove the duplicates if any,
* fill the dataGridView with the result and
* show the form3.
If I understood well, this is not working for you.

If this is right, on the button3 Click event you can do:

Dim Obj As New Form3
Obj.PassedText = TextBox1.Text
Obj.Show()
End Sub

Then on the form3 you need to define:

Private _passedText As String

Public Property [PassedText]() As String
Get
Return _passedText
End Get
Set(ByVal Value As String)
_passedText = Value
End Set
End Property

Then on the Form Load event of the form3 you should:

Dim strConn As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Nazneen\Master\Master\Masterdb.mdf;Integrated Security=True;User Instance=True"
Dim conn As New SqlConnection(strConn)
Dim da As New SqlDataAdapter("select * from SchemeKitMaster where filter = '" & _passedText & "';", conn)
Dim ds As New DataSet()
da.Fill(ds, "SchemeKitMaster")
' Filling a employee table 
Dim dt As DataTable = ds.Tables("SchemeKitMaster")
dt = RemoveDuplicateRows(dt, "month")
DataGridView1.DataSource = ds.Tables("SchemeKitMaster").DefaultView
DataGridView1.Refresh
conn.Close

all of this, assuming that filter is a valid field name wich content is to be compared with the text passed.

I am not undertanding why you remove all execept the first record returned for each month. If month is the only field in the table, then a SELEC DISTINCT * FROM .... will do the tric, and you will have not the need to manually remove the duplicates.

[naazsayed]

hey i solved that problem by creating a dataset and selecting appropriate values and displaying it :)

but now another problem has occured that have a kitmaster form in that fields like month ,year ,serialnp,quantity etc are present

for the first time if user opens this form it must accept all details and save it in database that works just fine this code is

If ComboBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Then
MessageBox.Show("Plz Enter Data first...", "Error", MessageBoxButtons.OK)
ElseIf ComboBox1.Text <> "" <> "" Or TextBox2.Text <> "" Or TextBox3.Text <> "" Then
Dim constr As String
'constr = System.Configuration.ConfigurationManager.AppSettings("ConnectionString")
constr = "Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Nazneen\Master\Master\Masterdb.mdf;Integrated Security=True;User Instance=True"
Dim conn As New SqlConnection(constr)
Try
conn.Open()
Dim query As String
query = "select * from SchemeKitMaster"
Dim da As New SqlDataAdapter(query, conn)
Dim ds As Data.DataSet = New Data.DataSet("SchemeKitMaster")
da.Fill(ds, "SchemeKitMaster")
Dim table As New Data.DataTable
table = ds.Tables("SchemeKitMaster")
Dim newrow As Data.DataRow
newrow = table.NewRow()
newrow("month") = ComboBox1.Text
newrow("year") = ComboBox2.Text
newrow("quantityfrm1") = TextBox2.Text
newrow("quantityto1") = TextBox3.Text
newrow("rs1") = TextBox4.Text
newrow("quantityfrm2") = TextBox5.Text
newrow("quantityto2") = TextBox6.Text
newrow("rs2") = TextBox7.Text
newrow("quantityfrm3") = TextBox8.Text
newrow("quantityto3") = TextBox9.Text
newrow("rs3") = TextBox10.Text
newrow("quantityfrm4") = TextBox11.Text
newrow("quantityto4") = TextBox12.Text
newrow("rs4") = TextBox13.Text
newrow("quantityfrm5") = TextBox14.Text
newrow("quantityto5") = TextBox15.Text
newrow("rs5") = TextBox16.Text
table.Rows.Add(newrow)
Dim builder As New SqlCommandBuilder(da)
da.InsertCommand = builder.GetInsertCommand()
da.Update(ds, "SchemeKitMaster")
ds.Clear()
da.Fill(ds, "SchemeKitMaster")
table = ds.Tables("SchemeKitMaster")
MsgBox("Data Has Been Saved Successfully....")
Catch ex As Exception
MsgBox(ex.Message)
Finally
conn.Close()
End Try
Else
MessageBox.Show("Record already exists", "Existing record", MessageBoxButtons.OK, MessageBoxIcon.Error)
'Else
' MessageBox.Show("invalid record...Try Again", "Invalid Data", MessageBoxButtons.RetryCancel)
End If
'ComboBox1.Text = ""
ComboBox1.Enabled = False
ComboBox2.Text = ""
'TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""
TextBox11.Text = ""
TextBox12.Text = ""
TextBox13.Text = ""
TextBox14.Text = ""
TextBox15.Text = ""
TextBox16.Text = ""

End Sub

but now the problem is that if same entry is reentered by the user that is if user selects month and year as previously entered it must display a message that "Entry already Exists"

what to do?

[naazsayed]

hey i solved that problem by creating a dataset and selecting appropriate values and displaying it :)

but now another problem has occured that have a kitmaster form in that fields like month ,year ,serialno,quantity etc are present

for the first time if user opens this form it must accept all details and save it in database that works just fine this code is

If ComboBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Then
MessageBox.Show("Plz Enter Data first...", "Error", MessageBoxButtons.OK)
ElseIf ComboBox1.Text <> "" <> "" Or TextBox2.Text <> "" Or TextBox3.Text <> "" Then
Dim constr As String
'constr = System.Configuration.ConfigurationManager.AppSettings("ConnectionString")
constr = "Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Nazneen\Master\Master\Masterdb.mdf;Integrated Security=True;User Instance=True"
Dim conn As New SqlConnection(constr)
Try
conn.Open()
Dim query As String
query = "select * from SchemeKitMaster"
Dim da As New SqlDataAdapter(query, conn)
Dim ds As Data.DataSet = New Data.DataSet("SchemeKitMaster")
da.Fill(ds, "SchemeKitMaster")
Dim table As New Data.DataTable
table = ds.Tables("SchemeKitMaster")
Dim newrow As Data.DataRow
newrow = table.NewRow()
newrow("month") = ComboBox1.Text
newrow("year") = ComboBox2.Text
newrow("quantityfrm1") = TextBox2.Text
newrow("quantityto1") = TextBox3.Text
newrow("rs1") = TextBox4.Text
newrow("quantityfrm2") = TextBox5.Text
newrow("quantityto2") = TextBox6.Text
newrow("rs2") = TextBox7.Text
newrow("quantityfrm3") = TextBox8.Text
newrow("quantityto3") = TextBox9.Text
newrow("rs3") = TextBox10.Text
newrow("quantityfrm4") = TextBox11.Text
newrow("quantityto4") = TextBox12.Text
newrow("rs4") = TextBox13.Text
newrow("quantityfrm5") = TextBox14.Text
newrow("quantityto5") = TextBox15.Text
newrow("rs5") = TextBox16.Text
table.Rows.Add(newrow)
Dim builder As New SqlCommandBuilder(da)
da.InsertCommand = builder.GetInsertCommand()
da.Update(ds, "SchemeKitMaster")
ds.Clear()
da.Fill(ds, "SchemeKitMaster")
table = ds.Tables("SchemeKitMaster")
MsgBox("Data Has Been Saved Successfully....")
Catch ex As Exception
MsgBox(ex.Message)
Finally
conn.Close()
End Try
Else
MessageBox.Show("Record already exists", "Existing record", MessageBoxButtons.OK, MessageBoxIcon.Error)
'Else
' MessageBox.Show("invalid record...Try Again", "Invalid Data", MessageBoxButtons.RetryCancel)
End If
'ComboBox1.Text = ""
ComboBox1.Enabled = False
ComboBox2.Text = ""
'TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""
TextBox11.Text = ""
TextBox12.Text = ""
TextBox13.Text = ""
TextBox14.Text = ""
TextBox15.Text = ""
TextBox16.Text = ""

End Sub

but now the problem is that if same entry is reentered by the user that is if user selects month and year as previously entered it must display a message that "Entry already Exists"

what to do?

[lolafuertes]

A simple way is to declare year and month primary keys in the SQL table definition. When you insert an existing one, the SQL will answer with an exception of duplicate key.

Another way is, before creating the new row, analyze the existing rows in the datatable (using a "for each / next") and if a row containing the month and year exist then show a message and exit the sub. Else, insert the new row.

Hope this helps

[naazsayed]

i dont want an exception to occur just want a message to come

and the data is like january 2011 and if again january 2011 entered it must say that this record exist already

[naazsayed]

Got it i created an index in database first and then insertion code

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

If ComboBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Then
MessageBox.Show("Plz Enter Data first...", "Error", MessageBoxButtons.OK)
ElseIf ComboBox1.Text <> "" Or TextBox2.Text <> "" Or TextBox3.Text <> "" Then
Dim constr As String
'constr = System.Configuration.ConfigurationManager.AppSettings("ConnectionString")
constr = "Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Nazneen\Master\Master\Masterdb.mdf;Integrated Security=True;User Instance=True"
Dim conn As New SqlConnection(constr)
Try
conn.Open()
Dim query As String
query = "select * from SchemeKitMaster"
'create index date_idx on SchemeKitMaster (month,year)
'create unique index date_idx on SchemeKitMaster (month,year)
Dim da As New SqlDataAdapter(query, conn)
Dim ds As Data.DataSet = New Data.DataSet("SchemeKitMaster")
da.Fill(ds, "SchemeKitMaster")
Dim table As New Data.DataTable
table = ds.Tables("SchemeKitMaster")
Dim newrow As Data.DataRow
newrow = table.NewRow()
newrow("month") = ComboBox1.Text
newrow("year") = ComboBox2.Text
newrow("quantityfrm1") = TextBox2.Text
newrow("quantityto1") = TextBox3.Text
newrow("rs1") = TextBox4.Text
newrow("quantityfrm2") = TextBox5.Text
newrow("quantityto2") = TextBox6.Text
newrow("rs2") = TextBox7.Text
newrow("quantityfrm3") = TextBox8.Text
newrow("quantityto3") = TextBox9.Text
newrow("rs3") = TextBox10.Text
newrow("quantityfrm4") = TextBox11.Text
newrow("quantityto4") = TextBox12.Text
newrow("rs4") = TextBox13.Text
newrow("quantityfrm5") = TextBox14.Text
newrow("quantityto5") = TextBox15.Text
newrow("rs5") = TextBox16.Text
table.Rows.Add(newrow)
Dim builder As New SqlCommandBuilder(da)
da.InsertCommand = builder.GetInsertCommand()
da.Update(ds, "SchemeKitMaster")
ds.Clear()
da.Fill(ds, "SchemeKitMaster")
table = ds.Tables("SchemeKitMaster")
MsgBox("Data Has Been Saved Successfully....")
Catch ex As Exception

MsgBox("Entry Already Exist.")
'MsgBox(ex.Message)
Finally
conn.Close()
End Try
' Else
' MessageBox.Show("Record already exists", "Existing record", MessageBoxButtons.OK, MessageBoxIcon.Error)
'Else
' MessageBox.Show("invalid record...Try Again", "Invalid Data", MessageBoxButtons.RetryCancel)
End If
'ComboBox1.Text = ""
ComboBox1.Enabled = False
ComboBox2.Text = ""
'TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""
TextBox11.Text = ""
TextBox12.Text = ""
TextBox13.Text = ""
TextBox14.Text = ""
TextBox15.Text = ""
TextBox16.Text = ""

End Sub

[naazsayed]

but still that above problem again occured inspite of the field available in database exception is thrown field doesnot belong to hte respective table

my code is

Dim constr As String
'constr = System.Configuration.ConfigurationManager.AppSettings("ConnectionString")
constr = "Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Nazneen\Master\Master\Masterdb.mdf;Integrated Security=True;User Instance=True"
Dim conn As New SqlConnection(constr)
Try
conn.Open()
Dim query As String

'query = "select * from purchasetable"
query = "SELECT available_bal,SUM(available_bal) FROM purchasetable WHERE available_bal=0 GROUP BY available_bal"
ConnectionState.Open.ToString()
Dim da As New SqlDataAdapter(query, conn)
Dim ds As Data.DataSet = New Data.DataSet("purchasetable")
da.Fill(ds, "purchasetable")
Dim table As New Data.DataTable
table = ds.Tables("purchasetable")
Dim newrow As Data.DataRow
newrow = table.NewRow()
newrow("available_bal") = TextBox8.Text
newrow("date") = TextBox1.Text
newrow("amt") = TextBox2.Text
newrow("discount") = TextBox3.Text
'newrow("bill_amt") = TextBox4.Text
newrow("invoice_date") = TextBox5.Text
newrow("invoice_no") = TextBox6.Text

table.Rows.Add(newrow)
Dim builder As New SqlCommandBuilder(da)
da.InsertCommand = builder.GetInsertCommand()
da.Update(ds, "purchasetable")
ds.Clear()
da.Fill(ds, "purchasetable")
table = ds.Tables("purchasetable")
MsgBox("Data Has Been Added Successfully....")
Catch ex As Exception
MsgBox(ex.Message)
Finally
conn.Close()
ConnectionState.Closed.ToString()
End Try
'TextBox1.Text = ""
'TextBox2.Text = ""
'TextBox3.Text = ""
'TextBox4.Text = ""
'TextBox5.Text = ""
'TextBox6.Text = ""
'TextBox7.Text = ""

End Sub

error says date is not a field of table

i googled the error it says its due to connection pooling not getting please explain

http://www.ksvali.com/2010/08/solution-to-random-error-column-does-not-belong-to-table/ this was the site

[lolafuertes]

No, your error is not related to the pooling.

In your example:

da.Fill(ds, "purchasetable")

is using the following 2 fiels for this table according to your select: available_bal and SUM(available_bal). This will create a table called "purchasetable" with only 2 columns.

Then you try to create a new row from the existing "purchasetable" with a lot of fields not existing in there:
newrow("date") = TextBox1.Text
newrow("amt") = TextBox2.Text
newrow("discount") = TextBox3.Text
'newrow("bill_amt") = TextBox4.Text
newrow("invoice_date") = TextBox5.Text
newrow("invoice_no") = TextBox6.Text

You need to be consistent in the table definitions.

Hope this helps

[naazsayed]

but i have created first table in database than wrote code isn't that right?

[lolafuertes]

Yes. That is right.
But...

The database you created is the phisical data container.
The Dataset you fill is the logical container for the extracted data of a database.

Each time you do a select from the phisical database to fill a datatable in the dataset, you are creating a partial logical view (even if it contains all the fields from the database). This logical view can have any name, but this will not guarantee that it has the same structure than the database. The structutre of fileds will be those included in the select clause.

The code

Dim ds As Data.DataSet = New Data.DataSet("purchasetable")

erases any previous definition of the datatable "purcharetable" and creates a new definition based on your

"SELECT available_bal,SUM(available_bal) FROM purchasetable WHERE available_bal=0 GROUP BY available_bal"

Please have in mind that the dataset is a virtual database that can be changed on the fly and that does not is the actual phisical database.

So as Mitja and I said before, you need to create a new and distinct logical view (datatable) from the same phisical database to be the datasource to be bound to each combo box: onecombobox - onedatatable, anothercombobox - anotherdatatable.

Hope this helps

[naazsayed]

can u say how to do that in same code?

[lolafuertes]

You need to re-dessign all your program. Is not possible to do it with the same code.

If you want, you can zip all your project and uplad it here inorder some one can read it and, if is possible, show you where you need to change the design or your code.