Vb6 + SQL Server 2008 R2

PingganMangkok

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: [Microsoft][ODBC SQL Server Driver][SQL Server] 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

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