Vb6 + SQL Server 2008 R2

PingganMangkok Publish time 12-5-2011 02:45 PM

assalamualaikum & slmt sejahtera

aku create application pakai vb6 dan database ms access. dan aku ingin migrate database dari ms access ke sql server 2008.
aku ada masalah error bila nak insert data ke sql server. error message spt berikut: Incorrect syntax near '12'

aku connect ke sql server 2008 r2 pakai DNS yg akan point ke sql server yg akan login ke server pakai server authentication.
aku dah enable user acess kat dalam sql server utk write, execute, delete data. so, aku assume masalah ni takde kaitan dgn sql server tp kaitan dgn source code. bila aku pakai database microsoft access, error ni tak keluar pon.
kat bawah ni aku paste source code.

Option Explicit
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub cmdSave_Click()
If Len(txtCustomerCode.Text) < 1 Or Len(txtCustomerCode.Text) < 1 Then
   MsgBox "Please Enter Customer Code"
   Exit Sub
End If

If IsCustomerCodeAlreadyInDatabase() Then
   MsgBox "Customer Already Exist In Database"
   Exit Sub
End If
If IsUpdateCustomerSuccessful() Then
   MsgBox "New Customer Has Been Successfully Added", vbInformation, "Update Completed"
   Call ClrScr
   Call InitCustomerList
   txtCustomerCode.SetFocus
Else
   MsgBox "Please Review Log File For Detail Of Error Message", vbCritical, "Update Failed"
End If

End Sub
Private Function IsCustomerCodeAlreadyInDatabase() As Boolean
IsCustomerCodeAlreadyInDatabase = False
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Dim itmX As ListItem

gszsqlstatement = "SELECT * From tblCustomer WHERE CustomerCode = '" + txtCustomerCode.Text + "' OR CustomerName = '" + txtCustomerName.Text + "'"

With rs
   .CursorLocation = adUseServer
   .CursorType = adOpenForwardOnly
   .LockType = adLockReadOnly
   .Open gszsqlstatement, gADOConn, , , adCmdText
   If Not .EOF Then
         IsCustomerCodeAlreadyInDatabase = True
   End If
   .Close
End With
Set rs = Nothing
End Function
Private Sub form_activate()
Static bLoaded As Boolean

If Not bLoaded Then
   bLoaded = True
   Call InitCustomerList
   Call InitCustCode
End If

End Sub
Private Sub InitCustomerList()

Dim itmX As ListItem
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

lvwCustomer.ListItems.Clear

gszsqlstatement = " SELECT * FROM tblCustomer Order By CustomerName "
With rs
   .CursorLocation = adUseServer
   .CursorType = adOpenForwardOnly
   .LockType = adLockReadOnly
   .Open gszsqlstatement, gADOConn, , , adCmdText

         Do Until .EOF
            Set itmX = lvwCustomer.ListItems.Add(, , lvwCustomer.ListItems.Count + 1)
            itmX.SubItems(1) = !CustomerName & ""
            itmX.SubItems(2) = !CustomerCode & ""
            .MoveNext
         Loop
   .Close
End With

Set rs = Nothing
End Sub
Private Sub InitCustCode()

Dim itmX As ListItem
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

gszsqlstatement = " SELECT * FROM tblControlSeq WHERE DataType = 'CustomerCode' "
With rs
   .CursorLocation = adUseServer
   .CursorType = adOpenForwardOnly
   .LockType = adLockReadOnly
   .Open gszsqlstatement, gADOConn, , , adCmdText

   If Not .EOF Then
            txtCustomerCode.Text = !DataValue
   End If
   .Close
End With

Set rs = Nothing
End Sub
Private Sub ClrScr()
   txtCustomerName.Text = ""
   txtCustomerCode.Text = ""
End Sub
Private Sub Form_KeyPress(KeyAscii As Integer)
Call NextDlgCtrl(KeyAscii)
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set frmAddCustomer = Nothing
End Sub
Private Function IsUpdateCustomerSuccessful() As Boolean
IsUpdateCustomerSuccessful = False

On Error GoTo ErrHandler

gszsqlstatement = " INSERT INTO tblCustomer (CustomerCode, CustomerName,CustomerType,CustomerDOName,DOStreet1,DOStreet2, " + _
                  " DOCity,DOState,DOCountry,CustomerInvName,InvStreet1,InvStreet2,InvCity,InvState,InvCountry,CustWebsite,ActiveCustomer, " + _
                  " PersonInCharge,OffTelNo,FaxTelNo) VALUES ('" + _
                  txtCustomerCode.Text + "', '" + txtCustomerName.Text + "','" + cboCustomerType.Text + "'" + _
                  ",'" + txtCustDeliveryName.Text + "','" + txtDOStreet1.Text + "','" + txtDOStreet2.Text + "'," + _
                  "'" + txtDOCity.Text + "','" + txtDOState.Text + "','" + cboDOCountry.Text + "','" + txtCustInvoiceName.Text + "'" + _
                  " ,'" + txtInStreet1.Text + "','" + txtInStreet2.Text + "','" + txtInCity.Text + "','" + txtInState.Text + "'" + _
                  " ,'" + cboInCountry.Text + "','" + txtWebSite.Text + "','" + cboActiveCust.Text + "','" + txtPerInCharge.Text + "'" + _
                  " ,'" + txtGeneralOffNo.Text + "','" + txtGeneralFaxNo.Text + "')"

gADOConn.BeginTrans
   gADOConn.Execute gszsqlstatement, , adExecuteNoRecords
gADOConn.CommitTrans

IsUpdateCustomerSuccessful = True

Exit Function

ErrHandler:
gADOConn.RollbackTrans
Call LogErrors("frmAddCustomer.IsUpdateCust...", gADOConn.Errors(0).Number, gADOConn.Errors(0).Description)
MsgBox "Error Encountered While Saving. Please Review Logi File", vbCritical, "Error"
End Function
Private Sub txtCustomerName_Change()
txtCustDeliveryName.Text = txtCustomerName.Text
txtCustInvoiceName.Text = txtCustomerName.Text
End Sub

bowring Publish time 19-5-2011 07:38 PM

Post Last Edit by bowring at 19-5-2011 19:39

Reply 1# PingganMangkok

-buka ms sql server ...
-pergi tool
-pastuh click profiler...

run profiler.... dan tgk kt mana SQL Statement yg sangkut...
maybe datatype column jenis BIT tp you insert INT
sbb tuh kuar... incorect syntax near '12'...

atau pon column value '12' tuh adalah foreign key kepada table lain...

check SQL statement

Pages: [1]

CARI Infonet's Archiver

Vb6 + SQL Server 2008 R2