Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Execution Failed

Author  Topic 

zahidbd83
Starting Member

2 Posts

Posted - 2013-06-11 : 15:02:26
These codes works properly. But I dont know why sometimes it fails.

--------------------------------------------------------------------
Imports System.Data.SqlClient

Module Module1
Dim con As SqlConnection
Public IDDD As String

Public Sub createConnection()
Dim C As String
C = My.Settings.StockConnectionString

con = New SqlConnection
con.ConnectionString = C
con.Open()
End Sub

Private Sub checkConnection()

If con Is Nothing OrElse con.State = ConnectionState.Closed Then
createConnection()
End If

End Sub

Public Function getDataReader(ByVal SQL As String) As SqlDataReader

checkConnection()
Dim cmd As New SqlCommand(SQL, con)
Dim dr As SqlDataReader
dr = cmd.ExecuteReader
Return dr

End Function

Public Function getDataTable(ByVal SQL As String) As DataTable

checkConnection()
Dim cmd As New SqlCommand(SQL, con)
Dim table As New DataTable
Dim da As New SqlDataAdapter(cmd)
da.Fill(table)
Return table

End Function

Public Sub executeQuery(ByVal SQL As String)
checkConnection()
Dim cmd As New SqlCommand(SQL, con)
cmd.ExecuteNonQuery()
End Sub
--------------------------------------------------------------------
Imports System.Data.SqlClient
Imports System.Windows.Forms

Public Class Client_Sale

Dim query, q1, q2, q3, q4, q5, q6 As String
Dim dr As SqlClient.SqlDataReader
Dim Qbalance, csbalance, cbalance As Integer

--------------------------------------------------------------------
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
query = "Select quantity From Pname Where productname = '" & ProductnameTextBox.Text & "'"
dr = getDataReader(query)
If (dr IsNot Nothing AndAlso dr.HasRows) Then
dr.Read()
End If
Qbalance = dr("quantity").ToString
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
If dr IsNot Nothing AndAlso Not dr.IsClosed Then
dr.Close()
End If
End Try

Try
query = "Select cbalance From client Where clientname = '" & ClientnameTextBox.Text & "'"
dr = getDataReader(query)
If (dr IsNot Nothing AndAlso dr.HasRows) Then
dr.Read()
End If
csbalance = dr("cbalance").ToString
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
If dr IsNot Nothing AndAlso Not dr.IsClosed Then
dr.Close()
End If
End Try


Try
q1 = "Update Client Set cbalance = cbalance + '" & Val(TextBox5.Text) & "' Where clientname = '" & ClientnameTextBox.Text & "'"
q2 = "Insert Into cdeal(clilink,ctype,cdeal,cdate,copen,cdebit,cclose) Values('" & ClientnameTextBox.Text & "','Sale','‡diZ " & ProductnameTextBox.Text & " (" & TextBox3.Text & "-" & TextBox4.Text & ")',GetDate(),'" & csbalance & "','" & Val(TextBox5.Text) & "','" & csbalance + Val(TextBox5.Text) & "')"
q3 = "Update PName Set quantity = quantity + '" & Val(TextBox3.Text) & "' Where productname = '" & ProductnameTextBox.Text & "'"
q5 = "Insert Into Pdeal(pnamelink,protype,pdeal,pdate,popen,pdebit,pclose) values('" & ProductnameTextBox.Text & "','Creturn','weµq ‡diZ (" & TextBox3.Text & "-" & TextBox4.Text & ")',GetDate(),'" & Qbalance & "','" & TextBox3.Text & "','" & Qbalance + Val(TextBox3.Text) & "')"
q6 = "Insert Into ACS(scom,spt,spn,sdate,stype,sdeal,sqty,staka) values('" & DataGridView3.CurrentCell.Value & "','" & DataGridView2.CurrentCell.Value & "','" & ProductnameTextBox.Text & "',GetDate(),'CC','weµq †diZ (" & ClientnameTextBox.Text & ")','" & -Val(TextBox3.Text) & "','" & -Val(TextBox5.Text) & "')"

executeQuery(q1)
executeQuery(q2)
executeQuery(q3)
executeQuery(q4)
executeQuery(q5)
executeQuery(q6)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-11 : 15:17:00
It is very hard, if not impossible for someone completely unfamiliar with your database, your business requirements, and your code to say what might be wrong. The following would help:

1. the EXACT text of the error message you get when it fails.
2. Sample data that goes into the database, preferably data as it exists at the time it fails
3. The DDL for the tables involved - see here for help on how to generate the DDL for the tables in a consumable format.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
4. Sample data to populate the database.

If you can provide at least some of these pieces, people on the forum would be able to offer suggestions.
quote:
Originally posted by zahidbd83

These codes works properly. But I dont know why sometimes it fails.

--------------------------------------------------------------------
Imports System.Data.SqlClient

Module Module1
Dim con As SqlConnection
Public IDDD As String

Public Sub createConnection()
Dim C As String
C = My.Settings.StockConnectionString

con = New SqlConnection
con.ConnectionString = C
con.Open()
End Sub

Private Sub checkConnection()

If con Is Nothing OrElse con.State = ConnectionState.Closed Then
createConnection()
End If

End Sub

Public Function getDataReader(ByVal SQL As String) As SqlDataReader

checkConnection()
Dim cmd As New SqlCommand(SQL, con)
Dim dr As SqlDataReader
dr = cmd.ExecuteReader
Return dr

End Function

Public Function getDataTable(ByVal SQL As String) As DataTable

checkConnection()
Dim cmd As New SqlCommand(SQL, con)
Dim table As New DataTable
Dim da As New SqlDataAdapter(cmd)
da.Fill(table)
Return table

End Function

Public Sub executeQuery(ByVal SQL As String)
checkConnection()
Dim cmd As New SqlCommand(SQL, con)
cmd.ExecuteNonQuery()
End Sub
--------------------------------------------------------------------
Imports System.Data.SqlClient
Imports System.Windows.Forms

Public Class Client_Sale

Dim query, q1, q2, q3, q4, q5, q6 As String
Dim dr As SqlClient.SqlDataReader
Dim Qbalance, csbalance, cbalance As Integer

--------------------------------------------------------------------
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
query = "Select quantity From Pname Where productname = '" & ProductnameTextBox.Text & "'"
dr = getDataReader(query)
If (dr IsNot Nothing AndAlso dr.HasRows) Then
dr.Read()
End If
Qbalance = dr("quantity").ToString
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
If dr IsNot Nothing AndAlso Not dr.IsClosed Then
dr.Close()
End If
End Try

Try
query = "Select cbalance From client Where clientname = '" & ClientnameTextBox.Text & "'"
dr = getDataReader(query)
If (dr IsNot Nothing AndAlso dr.HasRows) Then
dr.Read()
End If
csbalance = dr("cbalance").ToString
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
If dr IsNot Nothing AndAlso Not dr.IsClosed Then
dr.Close()
End If
End Try


Try
q1 = "Update Client Set cbalance = cbalance + '" & Val(TextBox5.Text) & "' Where clientname = '" & ClientnameTextBox.Text & "'"
q2 = "Insert Into cdeal(clilink,ctype,cdeal,cdate,copen,cdebit,cclose) Values('" & ClientnameTextBox.Text & "','Sale','‡diZ " & ProductnameTextBox.Text & " (" & TextBox3.Text & "-" & TextBox4.Text & ")',GetDate(),'" & csbalance & "','" & Val(TextBox5.Text) & "','" & csbalance + Val(TextBox5.Text) & "')"
q3 = "Update PName Set quantity = quantity + '" & Val(TextBox3.Text) & "' Where productname = '" & ProductnameTextBox.Text & "'"
q5 = "Insert Into Pdeal(pnamelink,protype,pdeal,pdate,popen,pdebit,pclose) values('" & ProductnameTextBox.Text & "','Creturn','weµq ‡diZ (" & TextBox3.Text & "-" & TextBox4.Text & ")',GetDate(),'" & Qbalance & "','" & TextBox3.Text & "','" & Qbalance + Val(TextBox3.Text) & "')"
q6 = "Insert Into ACS(scom,spt,spn,sdate,stype,sdeal,sqty,staka) values('" & DataGridView3.CurrentCell.Value & "','" & DataGridView2.CurrentCell.Value & "','" & ProductnameTextBox.Text & "',GetDate(),'CC','weµq †diZ (" & ClientnameTextBox.Text & ")','" & -Val(TextBox3.Text) & "','" & -Val(TextBox5.Text) & "')"

executeQuery(q1)
executeQuery(q2)
executeQuery(q3)
executeQuery(q4)
executeQuery(q5)
executeQuery(q6)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

Go to Top of Page

zahidbd83
Starting Member

2 Posts

Posted - 2013-06-12 : 06:45:33
it don't show any error message. It happens like
1 Zahid 5000
2 Ritu 4000
3 Rifat 7000
5 Bijoy 3000

as you can see that one of the records is missing. The program did not show any error message when the problem happens.

Notes:
* My sql connection is always open
* I am using 2 connections at a time. 1 for read and the other is for Insert, Update, Delete

Please Help
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-12 : 06:59:36
See this code part

Try
q1 = "Update Client Set cbalance = cbalance + '" & Val(TextBox5.Text) & "' Where clientname = '" & ClientnameTextBox.Text & "'"
q2 = "Insert Into cdeal(clilink,ctype,cdeal,cdate,copen,cdebit,cclose) Values('" & ClientnameTextBox.Text & "','Sale','‡diZ " & ProductnameTextBox.Text & " (" & TextBox3.Text & "-" & TextBox4.Text & ")',GetDate(),'" & csbalance & "','" & Val(TextBox5.Text) & "','" & csbalance + Val(TextBox5.Text) & "')"
q3 = "Update PName Set quantity = quantity + '" & Val(TextBox3.Text) & "' Where productname = '" & ProductnameTextBox.Text & "'"
q5 = "Insert Into Pdeal(pnamelink,protype,pdeal,pdate,popen,pdebit,pclose) values('" & ProductnameTextBox.Text & "','Creturn','weµq ‡diZ (" & TextBox3.Text & "-" & TextBox4.Text & ")',GetDate(),'" & Qbalance & "','" & TextBox3.Text & "','" & Qbalance + Val(TextBox3.Text) & "')"
q6 = "Insert Into ACS(scom,spt,spn,sdate,stype,sdeal,sqty,staka) values('" & DataGridView3.CurrentCell.Value & "','" & DataGridView2.CurrentCell.Value & "','" & ProductnameTextBox.Text & "',GetDate(),'CC','weµq †diZ (" & ClientnameTextBox.Text & ")','" & -Val(TextBox3.Text) & "','" & -Val(TextBox5.Text) & "')"

By looking at red marked params (variables) I think you missed out q4
But you are executing q4 also
--
Chandu
Go to Top of Page
   

- Advertisement -