Updating 2 SQL tables from 1 form

[bigmelon]

I am trying to update records coming from 2 different SQL tables on one form but i keep getting error messages. Here is my code:

Sub UpdateProduct()
' This sub is used to update and existing record with values
' from the form.
Dim cnSQL As SqlConnection
Dim cmSQL As SqlCommand
Dim strSQL As String
Dim intRowsAffected As Integer

Try
' Build update statement to update table with data
' on form.
strSQL = "UPDATE Pt_Ident SET" & _
" [6 Digit Hospital Number] = " & txtMRN.Text & _
" ,[Birth Date] = " & PrepareStr(txtBday.Text) & _
" ,Sex = " & PrepareStr(txtSex.Text) & _
" ,[Blood Type] = " & PrepareStr(txtBlood.Text) & _
" ,Race = " & PrepareStr(txtRace.Text) & _
" WHERE Social_Security_Number = " & PrepareStr(lblSSN.Text)

cnSQL = New SqlConnection("Server=*;Uid=*;" & _
"pwd=*;Database=*;")
cnSQL.Open()

cmSQL = New SqlCommand(strSQL, cnSQL)
intRowsAffected = cmSQL.ExecuteNonQuery()

If intRowsAffected <> 1 Then
MsgBox("Update Failed.", MsgBoxStyle.Critical, "Update")
End If

' Close and Clean up objects
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()

Catch e As SqlException
MsgBox(e.Message, MsgBoxStyle.Critical, "SQL Error")

Catch e As Exception
MsgBox(e.Message, MsgBoxStyle.Critical, "General Error")
End Try

'Second Update
Dim cnn As SqlConnection
Dim cmd As SqlCommand
Dim strSQL2 As String
Dim intRowsAffected1 As Integer
Dim Min As Integer
Dim Median As Integer
Dim OffPump As Integer
Dim ConvPump As Integer
Dim VATS As Integer

If chkMinInv.Checked = True Then
Min = 1
Else
Min = 0
End If

If chkMedian.Checked = True Then
Median = 1
Else
Median = 0
End If

If chkPump.Checked = True Then
OffPump = 1
Else
OffPump = 0
End If

If chkConvPump.Checked = True Then
ConvPump = 1
Else
ConvPump = 0
End If

If chkVATS.Checked = True Then
VATS = 1
Else
VATS = 0
End If

Try
' Build update statement to update table with data
' on form.
strSQL2 = "UPDATE SurgProc SET" & _
" Patient_Record_Number = " & txtARN.Text & _
" ,Date_Of_Admit = " & PrepareStr(txtAdmit.Text) & _
" ,Zip_Code = " & txtZip.Text & _
" ,Phone_Number = " & PrepareStr(txtPhoneNum.Text) & _
" ,Primary_Care_Physician = " & PrepareStr(cboPhysician.Text) & _
" ,Stress_Results = " & PrepareStr(txtStress.Text) & _
" ,Height = " & txtHeight.Text & _
" ,Weight = " & txtWeight.Text & _
" ,CCS_Class = " & PrepareStr(txtClass.Text) & _
" ,NYHA_Class = " & PrepareStr(txtNYHA.Text) & _
" ,Proc_Date = " & PrepareStr(txtProcDate.Text) & _
" ,Primary_Surgeon = " & PrepareStr(txtSurgeon.Text) & _
" ,Proc_Type = " & PrepareStr(txtProc.Text) & _
" ,Sequence_Num = " & txtSequence.Text & _
" ,Minimally_Invasive = " & Min & _
" ,Conv_to_stndrd_inc = " & Median & _
" ,Off_pump = " & OffPump & _
" ,Conv_to_pump = " & ConvPump & _
" ,Vats = " & VATS & _
" ,Urgency = " & PrepareStr(txtUrgency.Text) & _
" ,Cathdate = " & PrepareStr(txtCathDate.Text) & _
" ,Diag_Cath_Hosp = " & PrepareStr(cboCathHospital.Text) & _
" ,Referring_Physician = " & PrepareStr(cboAngio.Text) & _
" ,Ejection_Fraction = " & txtEF.Text & _
" ,EF_Source = " & PrepareStr(cboEFSource.Text) & _
" ,LVEDP = " & PrepareStr(txtLVEDP.Text) & _
" ,Cath_Cardiac_Output = " & txtCO.Text & _
" WHERE Social_Security_Number = " & PrepareStr(lblSSN.Text) & _
" ,AND Proc_Date = " & PrepareStr(txtProcDate.Text)

cnn = New SqlConnection("Server=*;Uid=*;" & _
"pwd=*;Database=*;")
cnn.Open()

cmd = New SqlCommand(strSQL2, cnn)
intRowsAffected = cmd.ExecuteNonQuery()

If intRowsAffected1 <> 1 Then
MsgBox("Update Failed.", MsgBoxStyle.Critical, "Update")
End If

' Close and Clean up objects
cnn.Close()
cmd.Dispose()
cnn.Dispose()

Catch e As SqlException
MsgBox(e.Message, MsgBoxStyle.Critical, "SQL Error")

Catch e As Exception
MsgBox(e.Message, MsgBoxStyle.Critical, "General Error")
End Try
MsgBox("Update Complete", MsgBoxStyle.OKOnly)
End Sub

Is this even the right way to go about 2 Updates from one form?

Any insight would be greatly appreciated.
Thanks,
melon

 

[Dimandja]

You said "error messages"? Which? Where?

 

[bduke]

I would move my updates after the connection

 

[bigmelon]

I get 2 different error messagses. Sometimes it says "Line 1: Incorrect syntax near ','" and other times it says "Incorrect syntax near keyword 'WHERE' " depending on which record I try to update.

thanks,
melon

 

[bigmelon]

I split the two apart in to two subs so i know where the second update is the one causing the error but i still cant figure out where

 

[jlively]

What I would do is pause the application just after the strSQL and then again after strSQL2 are built and copy the SQL statements into query analyzer and see if the problem is in the formatting of the SQL. If there are errors in the formatting query analyzer will tell you where they are. Also I assume that your function PrepareStr is handling things like apostrophe's in the text and removing the dashes from the SSN to make it a number?

 

[bigmelon]

thanks for the advice. i just decided to write the updates in sql instead cause it was a lot easier