Visual Basic

8-May-08 20:15

Hello everybody in the problem solving world !!!!.........

Well I have come across a unique problem and am not able to find any logic behind that.

My vb.net application runs on a sql server 2000 and it is about making bills in a fast running gift counter with 5 to 6 client and server is placed at a distance of 200 meters. The store sells maximum of 50-60 gifts. The user does all the transaction through clicks.

Two tables are involved for making a transaction. One is mastertable and detailtable.

In the mastertable my application is inserting the total of the bill, date, amount and the counter person’s code. It then generates an auto number which I picks up after the data is inserted in the mastertable and then uses the same in the detailtable.

The detailtable holds fields like item, qty, rate,id of mastertable etc.

My application is running smooth, but recently I noticed that say one in 500th time, row is not added to mastertable while it is present in detailtable with the id of mastertable. I have binded the sql with commit. I have digged my code but am not finding any logic behind this error.

Please help. Thanks in advance.

Christian Graus8-May-08 20:27

Christian Graus

8-May-08 20:27

If your detail table uses an id that refers to the master table, then the code to get that id must be broken, or the record is getting deleted. Do they have a foreign key relationship in your DB ?

Entering it all in a single transaction, in a stored proc, is the best way to do this

Anubhava Dimri8-May-08 20:54

8-May-08 20:54

Hi,

There are many Cases of Generation this type of Problem
Like
Many time Open Connection
Sql Query Error
Database Field Property Error

Can U Send Me Query and Database Table Property.I Can Halp U.

Sniff | :^)

Thanks

Anubhava Dimri
919250168195

vb_buddy9-May-08 20:24

9-May-08 20:24

Here is the code:

con = connect()
con.Open()

'transaction begins here-------
trans = con.BeginTransaction()

Try

'Data is inserted into mastertable---------

cmd = New SqlClient.SqlCommand("INSERT INTO mastertable(mbno,mbdate,mbamount,mbcperson) VALUES(" & counter_bill_no & ",'" & server_date & "'," & billvalue & "," & countercode & ")", con, trans)
cmd.ExecuteNonQuery()

cmd = New SqlClient.SqlCommand("select mbid from mastertable WHERE mbno=" & counter_bill_no & " and mbcperson=" & countercode & " ", con, trans)
mb_id = cmd.ExecuteScalar

'Data inserted into detailtable-----------

i = 0
For i = 0 To row_no - 1
selling_rate = DataGridView1.Item(2, i).Value
If selling_rate <> 0 Then
cmd = New SqlClient.SqlCommand("INSERT INTO detailtable(dbno,dbdate,dbicode,biquantity,dbamount) VALUES(" & mb_id & ",'" & server_date & "'," & DataGridView1.Item(4, i).Value & "," & DataGridView1.Item(1, i).Value & "," & DataGridView1.Item(3, i).Value & ")", con, trans)
cmd.ExecuteNonQuery()
End If
Next i

'Data commited--------

trans.Commit()

Catch ex As Exception

trans.Rollback()
MsgBox("Bill could not be generated, Please try again.")

End Try

con.Close()

vb_buddy9-May-08 20:27

9-May-08 20:27

Thanks.

Well my table as yet doesnt have the foreign key relationship. This is the code am using at present:

con = connect()
con.Open()

'transaction begins here-------
trans = con.BeginTransaction()

Try

'Data is inserted into mastertable---------

cmd = New SqlClient.SqlCommand("INSERT INTO mastertable(mbno,mbdate,mbamount,mbcperson) VALUES(" & counter_bill_no & ",'" & server_date & "'," & billvalue & "," & countercode & ")", con, trans)
cmd.ExecuteNonQuery()

cmd = New SqlClient.SqlCommand("select mbid from mastertable WHERE mbno=" & counter_bill_no & " and mbcperson=" & countercode & " ", con, trans)
mb_id = cmd.ExecuteScalar

'Data inserted into detailtable-----------

i = 0
For i = 0 To row_no - 1
selling_rate = DataGridView1.Item(2, i).Value
If selling_rate <> 0 Then
cmd = New SqlClient.SqlCommand("INSERT INTO detailtable(dbno,dbdate,dbicode,biquantity,dbamount) VALUES(" & mb_id & ",'" & server_date & "'," & DataGridView1.Item(4, i).Value & "," & DataGridView1.Item(1, i).Value & "," & DataGridView1.Item(3, i).Value & ")", con, trans)
cmd.ExecuteNonQuery()
End If
Next i

'Data commited--------

trans.Commit()

Catch ex As Exception

trans.Rollback()
MsgBox("Bill could not be generated, Please try again.")

End Try

con.Close()

As for Stored Procedures, i dont know much about it, what are the advantage of them.

Regards.

Anubhava Dimri11-May-08 18:23

11-May-08 18:23

Hi Dear,

Your Query is very good ? But you must to Check that what type of Data is going on the Database regarding the Database Properties.For this error you can use BreakPoint.

And For Date Value You Can Use # Operator. Just Like #12/12/2007#

My Modification on this Query

cmd = New SqlClient.SqlCommand("INSERT INTO mastertable(mbno,mbdate,mbamount,mbcperson) VALUES(" val(counter_bill_no) ",#" server_date "#," val( billvalue) "," val(countercode)")", con, trans)

Pls try and Reply

Blush | :O

Anubhava Dimri Hmmm | :|

919250168195

Mycroft Holmes8-May-08 20:50

Mycroft Holmes

8-May-08 20:50

Are you using a stored procedure to update the table (presumably with a transaction)

Put a foriegn key constraint on the detail table requirng a master table record. This willstop the orphan records being created in the database but will not stop the attempt. It may highlight when the transaction is failing.

When you say autonumber I presume you are using IDENTITY, this being returnd to the client for the child records to use.

A little more info about your operation (SQL) is required.

Never underestimate the power of human stupidity
RAH

vb_buddy9-May-08 20:29

Dataset field name in listbox

9-May-08 20:29

Thanks for the reply.

Here is the code:

con = connect()
con.Open()

'transaction begins here-------
trans = con.BeginTransaction()

Try

'Data is inserted into mastertable---------

cmd = New SqlClient.SqlCommand("INSERT INTO mastertable(mbno,mbdate,mbamount,mbcperson) VALUES(" & counter_bill_no & ",'" & server_date & "'," & billvalue & "," & countercode & ")", con, trans)
cmd.ExecuteNonQuery()

cmd = New SqlClient.SqlCommand("select mbid from mastertable WHERE mbno=" & counter_bill_no & " and mbcperson=" & countercode & " ", con, trans)
mb_id = cmd.ExecuteScalar

'Data inserted into detailtable-----------

i = 0
For i = 0 To row_no - 1
selling_rate = DataGridView1.Item(2, i).Value
If selling_rate <> 0 Then
cmd = New SqlClient.SqlCommand("INSERT INTO detailtable(dbno,dbdate,dbicode,biquantity,dbamount) VALUES(" & mb_id & ",'" & server_date & "'," & DataGridView1.Item(4, i).Value & "," & DataGridView1.Item(1, i).Value & "," & DataGridView1.Item(3, i).Value & ")", con, trans)
cmd.ExecuteNonQuery()
End If
Next i

'Data commited--------

trans.Commit()

Catch ex As Exception

trans.Rollback()
MsgBox("Bill could not be generated, Please try again.")

End Try

con.Close()

tchouny8-May-08 20:10

tchouny

8-May-08 20:10

Hi, I would like to get into a listbox all the field names from my access DB.
eg: fields ID, Name, Phone....
I have open a connection, created a dataset called Mydatatable and added data into a datagridview.
mydatatable = ds.Tables("test")
mydatagrid.DataSource = mydatatable
Then i've used LbListDB.DataSource = mydatagrid.Columns but here i get something not really nice like name + index...
I just want to get the name.
How can I do?? I suppose I have to use something like LbListDB.ValueMember= ??
Thx Tchouny

Re: Dataset field name in listbox

Anubhava Dimri8-May-08 20:44

Re: Dataset field name in listbox

8-May-08 20:44

You Can use secondory style

after open the connection with the Data base you can insert data One by one

While Rs.Read = true
listbox.items.add Rs.Reader("ABC");
end while

Anubhava Dimri
919250168195 $Shucks | :-\$

Rupesh Kumar Swami8-May-08 22:14

Rupesh Kumar Swami

8-May-08 22:14

hi,
use following statements

For i As Integer = 0 To mydatatable.Columns.Count - 1<br />
           LbListDB.items.add(mydatatable.Columns(i).ColumnName)<br />
       Next

hope this helps

Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)

My Company

Re: Dataset field name in listbox

tchouny18-May-08 23:41

tchouny

18-May-08 23:41

It works fine. Thx a lot.

which control to use on form in vb.net???

ketan bader8-May-08 19:55

ketan bader

8-May-08 19:55

I want to display and select multiple related data in one control.
i will have to update the selected data in database again.
can anyone suggest me what control to use on form.?? with code

Re: which control to use on form in vb.net???

Anubhava Dimri8-May-08 19:59