مشكة في كود فاتورة المبيعات

#123 مارس 2010 في 14:44 صاحب الموضوع

الرجاء من الاخوة الكرام اعلامي بالمشكلة في الكود التالي.

Private Sub frmCustomers5_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

On Error Resume Next

ConnectToLocalServerComputer() ds.EnforceConstraints = False

If Con.State = ConnectionState.Open Then Con.Close()

Con.Open()

Dim str1 As String = "SELECT * FROM SALES ORDER BY SLS1"

Dim str2 As String = "SELECT SITM1, SITM2, SITM3, SITM4, SITM5, SITM6, SITM7, (([sITM5] * [sITM6]) * (100 - [sITM7])) / 100 AS SITM8, SLS2 FROM SALESITEMS ORDER BY SITM2"

SqlDataAdapter1 = New SqlClient.SqlDataAdapter(str1, Con)

SqlDataAdapter2 = New SqlClient.SqlDataAdapter(str2, Con)

ds2.Clear()

SqlDataAdapter1.Fill(ds2, "SALES")

SqlDataAdapter2.Fill(ds2, "SALESITEMS")

ds2.Relations.Add("REL1", ds2.Tables("SALES").Columns("SLS2"), ds2.Tables("SALESITEMS").Columns("SLS2"), True)

BS.DataSource = ds2

BS.DataMember = "SALES"

ds2.EnforceConstraints = True

FILLCOMBOBOXITEMS("STOCKSITEMS", "SKITM5", SITM3)

Me.DataGridView1.DataSource = BS

Me.DataGridView1.DataMember = "REL1"

SqlDataAdapter1.Dispose()

SqlDataAdapter2.Dispose()

Con.Close()

RecordCount()

SumAmount()

SHOWBUTTON()

FILLCOMBOBOX("CUSTOMERS", "CMS2", Me.ComboBox1)

FILLCOMBOBOX("SALESEMPLOYEES", "SEMP2", Me.ComboBox3)

End Sub

Private Sub TEXT4_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TEXT4.TextChanged, TEXT5.TextChanged, TEXT6.TextChanged, TEXT7.TextChanged, TEXT8.TextChanged, TEXT9.TextChanged, TEXT10.TextChanged, TEXT11.TextChanged, TEXT12.TextChanged, TEXT13.TextChanged, TEXT14.TextChanged, TEXT15.TextChanged, TEXT16.TextChanged, TEXT17.TextChanged, TEXT18.TextChanged

On Error Resume Next

TEXT6.Text = Format(Val(TEXT4.Text) * Val(TEXT5.Text) / 100, "Fixed")

TEXT7.Text = Format(Val(TEXT4.Text) - Val(TEXT6.Text), "Fixed")

TEXT9.Text = Format(Val(TEXT7.Text) * Val(TEXT8.Text) / 1000, "Fixed")

TEXT11.Text = Format(Val(TEXT7.Text) * Val(TEXT10.Text) / 100, "Fixed")

TEXT14.Text = Format(Val(TEXT7.Text) * Val(TEXT13.Text) / 100, "Fixed")

TEXT16.Text = Format(Val(TEXT7.Text) * Val(TEXT15.Text) / 100, "Fixed")

TEXT18.Text = Format(Val(TEXT7.Text) * Val(TEXT17.Text) / 100, "Fixed")

TEXT19.Text = Format(Val(TEXT7.Text) - Val(TEXT9.Text) + Val(TEXT11.Text) + Val(TEXT12.Text) - Val(TEXT14.Text) + Val(TEXT16.Text) + Val(TEXT18.Text), "Fixed")

End Sub

Private Sub SumAmount()

Dim Adp As SqlClient.SqlDataAdapter

Dim con As SqlClient.SqlConnection

On Error Resume Next

Dim strsql As String = "SELECT Sum((SITM5 * SITM6) * (100 - SITM7) / 100 ) AS SUMSUBTOTALS FROM SALESITEMS WHERE SLS2 = " & Me.TEXT2.Text & " GROUP BY SLS2"

'con = New SqlClient.SqlConnection(constring)

Dim ds2 As New DataSet

Adp = New SqlClient.SqlDataAdapter(strsql, con)

ds2.Clear()

Adp.Fill(ds2)

If ds2.Tables(0).Rows.Count > 0 Then

Me.TEXT4.Text = ds2.Tables(0).Rows(0).Item(0)

End If

Adp.Dispose()

con.Close()

End Sub

Private Sub TEXT19_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TEXT19.TextChanged

On Error Resume Next

Me.TEXT20.Text = HANY(Val(TEXT19.Text), "EGYPT")

End Sub

Private Sub RecordCount()

On Error Resume Next

Dim TotalRecords, CurrenRecordst As Integer

Dim Back As Boolean = False

Dim Forward As Boolean = False

TotalRecords = BS.Count

CurrenRecordst = BS.Position + 1

RECORDSLABEL.Text = CurrenRecordst.ToString & " من " & TotalRecords.ToString

If BS.Position > 0 Then

Back = True

End If

If BS.Position < ds2.Tables("SALES").Rows.Count - 1 Then

Forward = True

End If

FIRSTBUTTON.Enabled = Back

PREVIOUSBUTTON.Enabled = Back

NEXTBUTTON.Enabled = Forward

LASTBUTTON.Enabled = Forward

DISPLAYRECORD()

End Sub

Private Sub DISPLAYRECORD()

On Error Resume Next

With Me

.TEXT1.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS1").ToString

.TEXT2.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS2").ToString

.TEXT3.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS4").ToString

.TEXT4.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS6").ToString

.TEXT5.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS7").ToString

.TEXT6.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS8").ToString

.TEXT7.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS9").ToString

.TEXT8.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS10").ToString

.TEXT9.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS11").ToString

.TEXT10.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS12").ToString

.TEXT11.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS13").ToString

.TEXT12.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS14").ToString

.TEXT13.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS15").ToString

.TEXT14.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS16").ToString

.TEXT15.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS17").ToString

.TEXT16.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS18").ToString

.TEXT17.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS19").ToString

.TEXT18.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS20").ToString

.TEXT19.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS21").ToString

.TEXT20.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS22").ToString

.TEXT21.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS24").ToString

.TEXT22.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS25").ToString

.TEXT23.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS26").ToString

.ComboBox1.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS5").ToString

.ComboBox3.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS23").ToString

.DATETIMEPICKER.Text = ds2.Tables("SALES").Rows(BS.Position)("SLS3").ToString

End With

End Sub

Private Sub bmp_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles BS.PositionChanged

On Error Resume Next

RecordCount()

End Sub

Private Sub SHOWBUTTON()

On Error Resume Next

Me.SAVEBUTTON.Enabled = BUTTON1

Me.DELETEBUTTON.Enabled = BUTTON3

Me.REVIEWERBUTTON.Enabled = BUTTON4

End Sub

Private Sub TEXT2_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TEXT2.KeyDown, TEXT1.KeyDown, TEXT3.KeyDown, TEXT4.KeyDown, TEXT5.KeyDown, TEXT6.KeyDown, TEXT7.KeyDown, TEXT8.KeyDown, TEXT9.KeyDown, TEXT10.KeyDown, TEXT11.KeyDown, TEXT12.KeyDown, TEXT13.KeyDown, TEXT14.KeyDown, TEXT15.KeyDown, TEXT16.KeyDown, TEXT17.KeyDown, TEXT18.KeyDown, TEXT19.KeyDown, TEXT20.KeyDown, TEXT21.KeyDown, TEXT22.KeyDown, TEXT23.KeyDown, DATETIMEPICKER.KeyDown, ComboBox1.KeyDown, ComboBox3.KeyDown

On Error Resume Next

Select Case e.KeyCode

Case Keys.Enter

SendKeys.SendWait("{TAB}")

End Select

End Sub

Private Sub ADDBUTTON_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ADDBUTTON.Click

Dim n As Double

n = BS.Count

On Error Resume Next

BS.EndEdit()

BS.AddNew()

CLEARDATA1(Me)

If n = 0 Then

Me.TEXT1.Text = 1

Else

Me.TEXT1.Text = Val(n) + 1

End If

TEXT1.Focus()

End Sub

Private Sub SAVEBUTTON_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SAVEBUTTON.Click

On Error Resume Next

Me.TEXT21.Text = USERNAME

Static Dim P As Integer

P = BS.Position

SumAmount()

UPDATESALES()

SaveSALESITEMS()

RecordCount()

frmCustomers5_Load(sender, e)

BS.Position = P

SumAmount()

SaveSALES()

SaveSALESITEMS()

End Sub

Private Sub SaveSALES()

On Error Resume Next

Dim SQL As String = "INSERT INTO SALES (SLS1, SLS2, SLS3, SLS4, SLS5, SLS6, SLS7, SLS8, SLS9, SLS10, SLS11, SLS12, SLS13, SLS14, SLS15, SLS16, SLS17, SLS18, SLS19, SLS20, SLS21, SLS22, SLS23, SLS24, SLS25, SLS26) VALUES (@SLS1, @SLS2, @SLS3, @SLS4, @SLS5, @SLS6, @SLS7, @SLS8, @SLS9, @SLS10, @SLS11, @SLS12, @SLS13, @SLS14, @SLS15, @SLS16, @SLS17, @SLS18, @SLS19, @SLS20, @SLS21, @SLS22, @SLS23, @SLS24, @SLS25, @SLS26)"

Dim CMD As SqlClient.SqlCommand = New SqlClient.SqlCommand

With CMD

.CommandType = CommandType.Text

.Connection = Con

.Parameters.Add("@SLS1", SqlDbType.Int).Value = Me.TEXT1.Text.Trim

.Parameters.Add("@SLS2", SqlDbType.NVarChar).Value = Me.TEXT2.Text.Trim

.Parameters.Add("@SLS3", SqlDbType.DateTime).Value = Me.DATETIMEPICKER.Text.Trim

.Parameters.Add("@SLS4", SqlDbType.NVarChar).Value = Me.TEXT3.Text.Trim

.Parameters.Add("@SLS5", SqlDbType.NVarChar).Value = Me.ComboBox1.Text.Trim

.Parameters.Add("@SLS6", SqlDbType.NVarChar).Value = Me.TEXT4.Text.Trim

.Parameters.Add("@SLS7", SqlDbType.NVarChar).Value = Me.TEXT5.Text.Trim

.Parameters.Add("@SLS8", SqlDbType.NVarChar).Value = Me.TEXT6.Text.Trim

.Parameters.Add("@SLS9", SqlDbType.NVarChar).Value = Me.TEXT7.Text.Trim

.Parameters.Add("@SLS10", SqlDbType.NVarChar).Value = Me.TEXT8.Text.Trim

.Parameters.Add("@SLS11", SqlDbType.NVarChar).Value = Me.TEXT9.Text.Trim

.Parameters.Add("@SLS12", SqlDbType.NVarChar).Value = Me.TEXT10.Text.Trim

.Parameters.Add("@SLS13", SqlDbType.NVarChar).Value = Me.TEXT11.Text.Trim

.Parameters.Add("@SLS14", SqlDbType.NVarChar).Value = Me.TEXT12.Text.Trim

.Parameters.Add("@SLS15", SqlDbType.NVarChar).Value = Me.TEXT13.Text.Trim

.Parameters.Add("@SLS16", SqlDbType.NVarChar).Value = Me.TEXT14.Text.Trim

.Parameters.Add("@SLS17", SqlDbType.NVarChar).Value = Me.TEXT15.Text.Trim

.Parameters.Add("@SLS18", SqlDbType.NVarChar).Value = Me.TEXT16.Text.Trim

.Parameters.Add("@SLS19", SqlDbType.NVarChar).Value = Me.TEXT17.Text.Trim

.Parameters.Add("@SLS20", SqlDbType.NVarChar).Value = Me.TEXT18.Text.Trim

.Parameters.Add("@SLS21", SqlDbType.NVarChar).Value = Me.TEXT19.Text.Trim

.Parameters.Add("@SLS22", SqlDbType.NVarChar).Value = Me.TEXT20.Text.Trim

.Parameters.Add("@SLS23", SqlDbType.NVarChar).Value = Me.ComboBox3.Text.Trim

.Parameters.Add("@SLS24", SqlDbType.NVarChar).Value = USERNAME

.Parameters.Add("@SLS25", SqlDbType.NVarChar).Value = ""

.Parameters.Add("@SLS26", SqlDbType.NVarChar).Value = ""

.CommandText = SQL

End With

If Con.State = ConnectionState.Open Then Con.Close()

Con.Open()

CMD.ExecuteNonQuery()

Con.Close()

End Sub

Private Sub SaveSALESITEMS()

On Error Resume Next

Dim SQL As String = "INSERT INTO SALESITEMS (SITM2, SITM3, SITM4, SITM5, SITM6, SITM7, SLS2) VALUES (@SITM2, @SITM3, @SITM4, @SITM5, @SITM6, @SITM7, @SLS2)"

Dim CMD As SqlClient.SqlCommand = New SqlClient.SqlCommand

With CMD

.CommandType = CommandType.Text

.Connection = Con

.Parameters.Add("@SITM2", SqlDbType.Int, 4, "SITM2")

.Parameters.Add("@SITM3", SqlDbType.NVarChar, 255, "SITM3")

.Parameters.Add("@SITM4", SqlDbType.NVarChar, 255, "SITM4")

.Parameters.Add("@SITM5", SqlDbType.Float, 8, "SITM5")

.Parameters.Add("@SITM6", SqlDbType.Float, 8, "SITM6")

.Parameters.Add("@SITM7", SqlDbType.Float, 8, "SITM7")

.Parameters.Add("@SLS2", SqlDbType.Int, 4, "SLS2")

.CommandText = SQL

End With

If Con.State = ConnectionState.Open Then Con.Close()

Con.Open()

SqlDataAdapter2.TableMappings.AddRange(New DataTableMapping() {New DataTableMapping("Table", "SALESITEMS", New DataColumnMapping() {New DataColumnMapping("SITM1", "SITM1"), New DataColumnMapping("SITM2", "SITM2"), New DataColumnMapping("SITM3", "SITM3"), New DataColumnMapping("SITM4", "SITM4"), New DataColumnMapping("SITM5", "SITM5"), New DataColumnMapping("SITM6", "SITM6"), New DataColumnMapping("SITM7", "SITM7"), New DataColumnMapping("SITM8", "SITM8"), New DataColumnMapping("SLS2", "SLS2")})})

SqlDataAdapter2.InsertCommand = CMD

SqlDataAdapter2.Update(ds2, "SALESITEMS")

Con.Close()

End Sub

Private Sub DELETEBUTTON_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DELETEBUTTON.Click

MYDELETERECORD("SALES", "SLS1", TEXT1, BS, True)

MYDELETERECORD("SALESITEMS", "SLS2", TEXT2, BS, True)

frmCustomers5_Load(sender, e)

End Sub

Private Sub REVIEWERBUTTON_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles REVIEWERBUTTON.Click

On Error Resume Next

Static Dim P As Integer

P = BS.Position

Me.TEXT22.Text = USERNAME

Me.TEXT23.Text = Format(Now.Date, "dd-MM-yyyy")

UPDATESALES()

frmCustomers5_Load(sender, e)

BS.Position = P

RecordCount()

End Sub

Private Sub FIRSTBUTTON_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FIRSTBUTTON.Click

On Error Resume Next

BS.Position = 0

RecordCount()

End Sub

Private Sub PREVIOUSBUTTON_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PREVIOUSBUTTON.Click

On Error Resume Next

BS.Position = BS.Position - 1

RecordCount()

End Sub

Private Sub NEXTBUTTON_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NEXTBUTTON.Click

On Error Resume Next

BS.Position = BS.Position + 1

RecordCount()

End Sub

Private Sub LASTBUTTON_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LASTBUTTON.Click

On Error Resume Next

BS.Position = BS.Count - 1

RecordCount()

End Sub

Private Sub EDITBUTTON_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EDITBUTTON.Click

On Error Resume Next

Me.TEXT21.Text = USERNAME

Static Dim P As Integer

P = BS.Position

SumAmount()

UPDATESALES()

UPDATESALESITEMS()

RecordCount()

frmCustomers5_Load(sender, e)

BS.Position = P

SumAmount()

UPDATESALES()

UPDATESALESITEMS()

End Sub

Private Sub UPDATESALES()

On Error Resume Next

Dim SQL As String = " Update SALES SET SLS1 = @SLS1, SLS2 = @SLS2, SLS3 = @SLS3, SLS4 = @SLS4, SLS5 = @SLS5, SLS6 = @SLS6, SLS7 = @SLS7, SLS8 = @SLS8, SLS9 = @SLS9, SLS10 = @SLS10, SLS11 = @SLS11, SLS12 = @SLS12, SLS13 = @SLS13, SLS14 = @SLS14, SLS15 = @SLS15, SLS16 = @SLS16, SLS17 = @SLS17, SLS18 = @SLS18, SLS19 = @SLS19, SLS20 = @SLS20, SLS21 = @SLS21, SLS22 = @SLS22, SLS23 = @SLS23, SLS24 = @SLS24, SLS25 = @SLS25, SLS26 = @SLS26 WHERE SLS1 = @SLS1"