SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Execution Failed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zahidbd83
Starting Member

Bangladesh
2 Posts

Posted - 06/11/2013 :  15:02:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3650 Posts

Posted - 06/11/2013 :  15:17:00  Show Profile  Reply with Quote
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


Edited by - James K on 06/11/2013 15:41:02
Go to Top of Page

zahidbd83
Starting Member

Bangladesh
2 Posts

Posted - 06/12/2013 :  06:45:33  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 06/12/2013 :  06:59:36  Show Profile  Reply with Quote
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

Edited by - bandi on 06/12/2013 07:00:22
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000