Database Help

[chris lynch]

Hi, i'm new here but i'm having alot of trouble using the SQL functions to update stuff in my database.... the code is below can anyone here help out and tell me whats wrong with it please......

Dim MyConn As ADODB.Connection
    Dim MyRecSet As ADODB.Recordset
    
    
    Set MyConn = New ADODB.Connection
    MyConn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=MS Access Database;Initial Catalog=D:\Documents and Settings\christopher.lynch\Desktop\Manual Time And attendance\Manualrecords.mdb"
    
    MyConn.Open
    MyConn.Execute ("UPDATE Manual SET '" & CmbClass.Text & "' = '" & CmbAttendance.Text & "' WHERE StudentName = '" & CmbStudent.Text & "'")
        
    
    MyConn.Close

What is ment to happen is, I have thre combe boxes with selection. I what to select a name to alter, class for that name and whether the student is absent or present.....

[chris lynch]

The error i get is too few parameters on the linew containing the MyConn.Execute ("UPDATE.....

Please halp i've been at this part of my program for almst 2 days now!!!!!!

[szlamany]

Welcome to the forum.

Although I believe you can use the .EXECUTE method of the connection, we do not do it that way...

We create a command object...

something like this:

Dim objCmd as ADODB.Command

Set objCmd = New ADODB.Command

objCmd.ActiveConnection = myConn
objCmd.CommandText = "UPDATE Manual SET '" & CmbClass.Text & "' = '" & CmbAttendance.Text _
    & "' WHERE StudentName = '" & CmbStudent.Text & "'"

objCmd.CommandType = adCmdText
objCmd.Execute

I typed that free hand - so it could have syntax errors...

HTH.

[chris lynch]

Ya i found that in a tutorial and this is the code i have now

Dim MyConn As ADODB.Connection
    Dim MyRecSet As ADODB.Recordset
    Dim strSQL As String
    
    
    Set MyConn = New ADODB.Connection
    MyConn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=MS Access Database;Initial Catalog=D:\Documents and Settings\christopher.lynch\Desktop\Manual Time And attendance\Manualrecords.mdb"
    MyConn.Open
    sSQL = ("UPDATE Manual SET '" & CmbClass.Text & "' = '" & CmbAttendance.Text & "' WHERE StudentName = '" & CmbStudent.Text & "'")
    
    
    
    Debug.Print strSQL
    
    MyConn.Execute strSQL
    MyConn.Close

do i take out the Top two lines or replace the Myconn and MySetRec with the sSQL?

[szlamany]

sSQL is a typo - you mean strSql - right?

You have no need for the recordset object - as the UPDATE statement can only be executed - it does not return a recordset.

[chris lynch]

Yes strSQL and take out the recordset got ya i'll be back in a mo with errors if any

Thanks

[chris lynch]

This is the error screen that appears

Run Time Error 3001
Arguments are of the wrong type, are out of acceptable range or are in conflict with one another....

this is the code i now have and the error occurs on the line
objCmd.ActiveConnection = MyConn

Dim objCmd As ADODB.Command
 
    Set objCmd = New ADODB.Command
 
    objCmd.ActiveConnection = MyConn
    objCmd.CommandText = "UPDATE Manual SET '" & CmbClass.Text & "' = '" & CmbAttendance.Text & "' WHERE StudentName = '" & CmbStudent.Text & "'"
 
    objCmd.CommandType = adCmdText
    objCmd.Execute

[szlamany]

Do you still have this code - because it's needed to open the connection...

Dim MyConn As ADODB.Connection
    Set MyConn = New ADODB.Connection
    MyConn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=MS Access Database;Initial Catalog=D:\Documents and Settings\christopher.lynch\Desktop\Manual Time And attendance\Manualrecords.mdb"
    MyConn.Open

[chris lynch]

Sorry about that just out it back in and now it says too few parameters for the objCmd.Execute line

[szlamany]

Please post the results of the

DEBUG.PRINT strSQL

[chris lynch]

This might be a stupid question but i've taken out strSQL so will i put in debug.print objCmd.CommandText

This is the code i have now

Dim MyConn As ADODB.Connection
    Dim objCmd As ADODB.Command
    
    Set MyConn = New ADODB.Connection
    MyConn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=MS Access Database;Initial Catalog=D:\Documents and Settings\christopher.lynch\Desktop\Manual Time And attendance\Manualrecords.mdb"
    MyConn.Open
 
    Set objCmd = New ADODB.Command
 
    objCmd.ActiveConnection = MyConn
    objCmd.CommandText = "UPDATE Manual SET '" & CmbClass.Text & "' = '" & CmbAttendance.Text & "' WHERE StudentName = '" & CmbStudent.Text & "'"
 
    objCmd.CommandType = adCmdText
    objCmd.Execute
    
    MyConn.Close

[szlamany]

I like the strSQL technique - that way you can build the string and look at it more easily...

strSQL = "UPDATE Manual SET '" & CmbClass.Text & "' = '" & CmbAttendance.Text & "' WHERE StudentName = '" & CmbStudent.Text & "'"
Debug.Print strSQL
objCmd.CommandText = strSQL

We don't do ACCESS here (we are a MS SQL Server shop) - but don't you need a semi-colon at the end of an access query?

Shouldn't the actual statement be:

strSQL = "UPDATE Manual SET '" & CmbClass.Text & "' = '" & CmbAttendance.Text & "' WHERE StudentName = '" & CmbStudent.Text & "';"

But at any rate, do the DEBUG.PRINT and paste the IMMEDIATE WINDOW contents here on the forum - thanks...

[chris lynch]

UPDATE Manual SET Physics = 'Absent' WHERE StudentName = 'Chris Lynch'

This is what i get, which looks right but its not doin that..... From the line above it should right absent the field called physics for the name chris lynch

[szlamany]

You actually have a column called PHYSICS in the TABLE called MANUAL?

Does that query statement execute in whatever kind of QUERY BUILDER that ACCESS has for you to test these things (remember I don't use ACCESS)...

And once again - doesn't ACCESS require a semi-colon at the end of a QUERY?

[chris lynch]

yes i have column called physics and a table called manual

I don't know what you mean by this as its my first time ever using access or databases even at that "Does that query statement execute in whatever kind of QUERY BUILDER that ACCESS has for you to test these things (remember I don't use ACCESS)..."

And in another forum i was using to find help they said nothing about a semi colon

[si_the_geek]

In your code you have:
...SET '" & CmbClass.Text & "' = '" ...

Shouldnt it be this instead?:
...SET [" & CmbClass.Text & "] = '" ...

(or this: ...SET " & CmbClass.Text & " = '" ... )

[szlamany]

UPDATE Manual SET Physics = 'Absent' WHERE StudentName = 'Chris Lynch'

This is what i get, which looks right but its not doin that..... From the line above it should right absent the field called physics for the name chris lynch

SI is right - you should not have the ' quotes around the column name...

But then again if you copy/pasted the immediate window into this post - you didn't at that time...

But it appears from the code in your last post that you do...

I'm confused by what you have posted.