We help IT Professionals succeed at work.

sql cant find  input table or query error

sql cant find  input table or query error:
 


Using the sql below:  I keep getting a error message on the syntax ?


strsql_sql = "INSERT INTO [dbo_t_nsc_trackcode_AdminLogIn_and_AdminLogOut_Daily]([NSC_ID_Racfid], [Admin_Logged_Out])" & vbCrLf
strsql_sql = strsql_sql & " VALUES('" & racfid & "', #" & dCurr & "#) where (DateValue([Admin_Logged_In]) = Date and [NSC_ID_Racfid] = '" & racfid & "');"


Thanks
fordraiders
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

When using VALUES, there cannot be a WHERE clause.


A WHERE clause somes into play when you "insert into select".

Author

Commented:
net, ok confused..isnt this a INSERT INTO SELECT statement ?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

I don't see the word "select".


Inserts have two forms:

insert into some_table values('val1','val1');


and

insert into some_table select some_col1, some_col2 from some_other_table;


You cannot mix and match.

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Probably you want
INSERT INTO  [dbo_t_nsc_trackcode_AdminLogIn_and_AdminLogOut_Daily]([NSC_ID_Racfid], [Admin_Logged_Out])
SELECT [NSC_ID_Racfid], [Admin_Logged_Out]
FROM YOURTABLE
WHERE (DateValue([Admin_Logged_In]) = Date and [NSC_ID_Racfid] = '" & racfid & "')

Open in new window

CERTIFIED EXPERT
Top Expert 2014

Commented:
You are missing a concatenation operator here

vbCrLf
strsql_sql

Your field names are not in the correct format.

Author

Commented:
john, net

OK still confused on inserting a parameter value ? or variables ?  

VALUES('" & racfid & "', #" & dCurr & "#)


so this cant be done ?

fordraiders
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

You can insert hard-coded values.


In the original SQL you posted, you have "VALUES" and "WHERE" in the string.  I'm saying you cannot have "VALUES" and "WHERE" in the same insert statement.

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
VALUES is only needed when you already have them as statically set...if you want a dynamic insert then you don't use VALUES as the Select will feed the insert query.

Author

Commented:
hmmm,  I guess and Update statement would be more approriate ?

strsql_sql = "UPDATE [dbo_t_nsc_trackcode_AdminLogIn_and_AdminLogOut_Daily] SET [Admin_Logged_Out] = #" & dCurr & "# WHERE (DateValue([Admin_Logged_In]) = Date and [NSC_ID_Racfid] = '" & racfid & "');"


fordraiders
CERTIFIED EXPERT
Top Expert 2014

Commented:
that looks like a correctly formatted Update statement string.
Software & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
Yes Update will work with WHERE
Take a look here for
INSERT INTO
INSERT INTO SELECT

Author

Commented:
getting "too few parameters expected one"  ?

table name is correct
field names are correct

strsql_sql = "UPDATE [dbo_t_nsc_trackcode_AdminLogIn_and_AdminLogOut_Daily] SET [Admin_Logged_Out] = #" & dCurr & "# WHERE (DateValue([Admin_Logged_In]) = Date) and [NSC_ID_Racfid] = '" & racfid & "'"
CERTIFIED EXPERT
Top Expert 2014

Commented:
please post the contents of the strsql_sql variable.

Author

Commented:
thanks all...used the update statement.