ADO

[smh]

I am trying to update a record in a SQL database.
This is a snip of my code:

With rst
.Update

rst("bankname") = cboBankName.Text
rst("address1") = txtBankAddress1

.Update
End With

I used the exact code in another program that I have made and it worked just fine. Now, when I get to the second ".Update", it says that I can't enter a NULL value into the field "bankname". When I run the program, there is data stored in 'cboBankName.Text' and all the other textboxes. Where am I going wrong?

(I used an Access database with the other project. Could I be getting an error because I am using SQL?)

[Edneeis]

One way to fix it is just to change the Bankname field to accept Null values. Otherwise you shouldn't need an update before you just the values just after.

[smh]

I can't allow the Bankname to allow NULL values. It's the primary key. Anyway, it shows that the bank's name is being inserted into the field bankname when I insert a break in the program...

[paulw]

Hi smh,

NO NO NO - NEVER USE NULL - Ed, you must be cast into the everlasting flames of Nullity...

To be a bit more helpful, put

If IsNull(Me.cboBankName.Text) Then
  rst("bankname") = "Missing"
Else
  rs("bankname") = Me.cboBankName.Text
End If

I would test the value of cboBankName.Text explicitly for Nullity and see whether the value is getting returned properly.

Cheers,

P.

[smh]

I tried inserting your code PaulW, and it's still giving me the same answer. Do you want to see more of the code?

[monte96]

1) Post more of the code

2) remove the 1st update

3) where is this code run? Is it in a module? In the form? Is the combobox out of scope?

[paulw]

In particular, how are you defing 'rst' in the first place?

Cheers,

P.

[smh]

Here's what I've got:

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
________________________________________

Private Sub Form_Load()

cnn.Open "Driver={SQL Server};Server=Brick;Database=ATM_Information;Uid=sa;Pwd=;"


________________________________________

Private Sub cboBankName_click()

gstrHold = cboBankName.Text

rst.Open "SELECT * from banks WHERE BankName = '" & gstrHold & "' ;", cnn, adOpenKeyset, adLockOptimistic

cboBankName = rst!bankname
txtBankAddress1 = rst!address1
....(Cont.)
End Sub
_________________________________________

Private Sub cmdUpdate_Click()

With rst
If IsNull(cboBankName.Text) Then
rst("bankname") = "Missing"
Else
rst("bankname") = cboBankName.Text
End If

rst("address1") = txtBankAddress1
....(Cont)

.Update
End With

temp = MsgBox("Update completed successfully.", vbOKOnly, "Update")

End Sub
____________________________________________

(I never close rst until the from is closed)

[paulw]

I am a bit worried about this one. It looks like the code is OK but what exactly is the error message?

Are you creating a new record and posting an implicit Update?

Cut and paste all of your code please. Any ideas Monte96?

Cheers,

P.

[smh]

Option Explicit

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rsd As New ADODB.Recordset
Dim contact As New ADODB.Recordset
Dim cont As New ADODB.Recordset


Dim temp As String
Dim sql As String




Private Sub cboBankName_click()

If rst.State <> adStateClosed Then
rst.Close
End If

If rsd.State <> adStateClosed Then
rsd.Close
End If

If contact.State <> adStateClosed Then
contact.Close
End If

gstrHold = cboBankName.Text

rst.Open "SELECT * from banks WHERE BankName = '" & gstrHold & "' ;", cnn, adOpenKeyset, adLockOptimistic

'Predefined subroutine
FillBankInfo

rsd.Open "SELECT * FROM BankFees WHERE BankName = '" & gstrHold & "'" & _
"AND feeenddate is NULL;", cnn, adOpenKeyset, adLockOptimistic

'Predefined subroutine
FillBankFees

cboName.Clear

contact.Open "SELECT * FROM BanksContacts " & _
"WHERE BankName = '" & gstrHold & "';", cnn, adOpenStatic, adLockOptimistic

cboName = contact!contact
txtPhone = contact!phone
txtExtension = contact!extension & ""
txtFax = contact!fax & ""
txtEmail = contact!EMail & ""

contact.Close

'Predefined subroutine
LoadContacts

End Sub
Private Sub LoadContacts()

gstrHold = cboBankName.Text

contact.Open "SELECT * FROM BanksContacts " & _
"WHERE BankName = '" & gstrHold & "';", cnn, adOpenStatic, adLockOptimistic

While Not contact.EOF
cboName.AddItem contact("contact")

contact.MoveNext
Wend

contact.Close

End Sub

Private Sub cboName_click()


If contact.State <> adStateClosed Then
contact.Close
End If

gstrContact = cboName.Text

contact.Open "SELECT * FROM BanksContacts " & _
"WHERE Contact = '" & gstrContact & "' ;", cnn, adOpenKeyset, adLockOptimistic

'Predefined subroutine
ChangeContacts

End Sub


Private Sub cmdClose_Click()

Set contact = Nothing
Set rsd = Nothing
Set rst = Nothing
Set cnn = Nothing

Unload frmEditBanks

End Sub

Private Sub ChangeContacts()

gstrHold = cboBankName.Text

'Fill in Contact information

cont.Open "SELECT Contact FROM BanksContacts " & _
"WHERE BankName = '" & gstrHold & "';", cnn, adOpenKeyset, adLockOptimistic


cboName = contact!contact
txtPhone = contact!phone
txtExtension = contact!extension & ""
txtFax = contact!fax & ""
txtEmail = contact!EMail & ""

cont.Close
Set cont = Nothing

End Sub

Private Sub FillBankInfo()

cboBankName = rst!bankname
txtBankAddress1 = rst!address1
txtBankAddress2 = rst!address2 & ""
txtBankCity = rst!City
txtBankState = rst!State
txtBankZip = rst!zip
txtExtAcctNo = rst!extaccountno & ""
txtIntAcctNo = rst!intaccountno & ""
txtWireABANo = rst!wireaba & ""
txtACHABANo = rst!achaba & ""
txtBankComments = rst!Comments & ""
End Sub

Private Sub FillBankFees()

txtAcctMaint = rsd!accountmaintfee & ""
txtVaultperDeposit = rsd!accountmaintfee & ""
txtVaultperDeposit = rsd!vaultperdepositfee & ""
txtElecCredit = rsd!electroniccreditfee & ""
txtElecDebit = rsd!electronicdebitfee & ""
txtIncomingWire = rsd!incomingwirefee & ""
txtCashOrderFee = rsd!cashorderfee & ""
txtCashOrderBasis = rsd!cashorderchargebasis & ""
txtEarnCredit = rsd!earningscredit & ""
txtFDICInsure = rsd!fdicinsurance & ""
txtOther = rsd!misc & ""

End Sub

Private Sub cmdUpdate_Click()

With rst
UpdateBankInfo
.Update
End With

With rsd
UpdateBankFees
.Update
End With

With contact
UpdateBankContact
.Update
End With

temp = MsgBox("Update completed successfully.", vbOKOnly, "Update")

End Sub
Private Sub UpdateBankFees()

rsd("accountmaintfee") = txtAcctMaint
rsd("vaultperdepositfee") = txtVaultperDeposit
rsd("electroniccreditfee") = txtElecCredit
rsd("electronicdebitfee") = txtElecDebit
rsd("incomingwirefee") = txtIncomingWire
rsd("cashorderfee") = txtCashOrderFee
rsd("cashorderchargebasis") = txtCashOrderBasis
rsd("earningscredit") = txtEarnCredit
rsd("fdicinsurance") = txtFDICInsure
'rsd("misc") = txtOther

End Sub
Private Sub UpdateBankInfo()

If IsNull(cboBankName.Text) Then
rst("bankname") = "Missing"
Else
rst("bankname") = cboBankName.Text
End If

rst("address1") = txtBankAddress1
rst("address2") = txtBankAddress2
rst("city") = txtBankCity
rst("state") = txtBankState
rst("zip") = txtBankZip
rst("extaccountno") = txtExtAcctNo
rst("intaccountno") = txtIntAcctNo
rst("wireaba") = txtWireABANo
rst("achaba") = txtACHABANo
rst("comments") = txtBankComments


End Sub
Private Sub UpdateBankContact()

rst("contact") = cboName.Text
rst("phone") = txtPhone
rst("extension") = txtExtension
rst("fax") = txtFax
rst("email") = txtEmail


End Sub



Private Sub Form_Load()

'Open an ADO Connection to retreive Bank data
cnn.Open "Driver={SQL Server};Server=Brick;Database=ATM_Information;Uid=sa;Pwd=;"



'Fill in BankName drop-down list
'_________________________________________

'Open the ADO Recordset
rst.Open "Select BankName from Banks", cnn

While Not rst.EOF
cboBankName.AddItem rst("BankName")

rst.MoveNext
Wend

rst.Close
Set rst = Nothing




End Sub

[smh]

Ooops...forgot to answer your other question. I am trying to update current records on this form.

[smh]

Here is the exact error:

Run-time error '-2147217873 (80040e2f)';

Cannot insert the value NULL into column 'BankName', table 'ATM_Information.dbo.BankFees'; Column does not allow nulls. INSERT fails.

[paulw]

Aha - looks like you have a cascading update - you update Banks and BankFees is updated automatically. I will check through, but this looks like a relational integrity problem.

Does that strike any bells?

I'll get back to you tomorrow.

P.

[smh]

Thanks

[monte96]

Another possibly related problem, is that you are freeing your reference to the rst recordset on Form Load.

Private Sub Form_Load()

'Open an ADO Connection to retreive Bank data
    cnn.Open "Driver={SQL Server};Server=Brick;Database=ATM_Information;Uid=sa;Pwd=;"

'Fill in BankName drop-down list
'_________________________________________

'Open the ADO Recordset
    rst.Open "Select BankName from Banks", cnn

    While Not rst.EOF
        cboBankName.AddItem rst("BankName")
        rst.MoveNext
    Wend

    rst.Close
    Set rst = Nothing '<--Remove this line!!
End Sub

Then you are referencing it again AFTER it is set to nothing. I didn't see a new reference set anywhere else in your code:

Private Sub cmdUpdate_Click()
    With rst'<- At this point rst = Nothing
        UpdateBankInfo
        .Update
    End With

    With rsd
        UpdateBankFees
        .Update
    End With

    With contact
        UpdateBankContact
        .Update
    End With

    temp = MsgBox("Update completed successfully.", vbOKOnly, "Update")
End Sub

AND finally, why are you using ODBC instead of OLEDB?

Change your connection string to:

cnn.Open "Provider=SQLOLEDB;Server=Brick;Database=ATM_Information;Uid=sa;Pwd=;"