Incorrect syntax 1

[cumap]

Hi all,

I looked back and forth, still don't know why I get this ERROR:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '='.

and the line that it specifies is

Set rs1 = objConn.Execute(sql2)

here is my code,

sql = "SELECT DISTINCT(ProductID) FROM tblSupports"
Set rs = objConn.Execute(sql)
Do while not rs.eof
	sql2 = "SELECT * FROM tblSupports WHERE ProductID = " & rs("ProductID")
	Set rs1 = objConn.Execute(sql2)
	sqlADD = "INSERT INTO tblSupports2(ManuID, HardwareID, CatID, ProductID, " _
			& "		FAQ, Answer, Specsheet, Manual, Demo, Driver, Utility, Firmware" _
			& ") VALUES (" _
			&       rs1("ManuID")			& " ," _
			&       rs1("HardwareID")		& " ," _
			&       rs1("Category")			& " ," _
			&       rs1("ProductID")		& " ," _
			& "'" & rs1("FAQ")				& "'," _
			& "'" & rs1("Answer")			& "'," _
			& "'" & rs1("SpecsheetFileName")& "'," _
			& "'" & rs1("ManualFileName")	& "'," _
			& "'" & rs1("DemoFileName")		& "'," _
			& "'" & rs1("DriverFileName")	& "'," _
			& "'" & rs1("Utility")			& "'," _
			& "'" & rs1("Firmware")			& "' " _
			& ")"
	objConn.Execute sqlADD
	rs.movenext
loop
call closeRS(rs)
call closeRS(rs1)

Thanks in advance.

 

[gmmastros]

Whoa. Holy loopy code batman!

Are you trying to take all the data from the tblSupports table and put it in to the tblSupports2 table? And you're using SQL Server? There's a much better way.

INSERT INTO tblSupports2(ManuID, HardwareID, CatID, ProductID, FAQ, Answer, Specsheet, Manual, Demo, Driver, Utility, Firmware)
Select ManuID, HardwareID, CatID, ProductID, FAQ, Answer, Specsheet, Manual, Demo, Driver, Utility, Firmware
From tblSupports

-George

"the screen with the little boxes in the window." - Moron

 

[gmmastros]

Oh... I should also mention that the reason you got the error in the first place is because you probably have a record in the tblSupports with a blank ProductId, so the resulting query (after the concatenation) became...

[tt][blue]SELECT * FROM tblSupports WHERE ProductID = [/blue][/tt]

-George

"the screen with the little boxes in the window." - Moron

 

[cumap]

Perfect!!!

Thank you very much for the much much better way to accomplish my goal.

 

[Chopstik]

And in relation to your original error message, your problem lies here:

sql2 = "SELECT * FROM tblSupports WHERE ProductID = " & [COLOR=red]rs("ProductID")[/color]

Chances are, if you do a response.write on your SQL before you attempt to run it, you'll find that you have no value for your rs("ProductID"). Try to call it without the parentheses, or alias it.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Chopstik]

Sheesh, I feel slow... ;-)

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[gmmastros]

Chops.... That's just your old age. Don't worry about it.

-George

"the screen with the little boxes in the window." - Moron

 

[Chopstik]

Actually, I feel rather spry for my "advanced" age... Or, should I say something along the lines of the pot calling the kettle black?...

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[gmmastros]

-George

"the screen with the little boxes in the window." - Moron

 

[cumap]

you guys are great!!! Thanks alot for the knowledge!!!

 

[cumap]

I'm sorry guys, please let me drag you back to the problem I have on the above. I was so happy with the short and sweet code George provided and totally forgot the reason why I came up with the whole nine-yard loop at the beginning. The reason is I want to reform the table with a little change in data storage such as this new table's field name Specsheet will have spec1,spec2,spec3 compare to the original one only has 1 spec at a time. That's the reason why I had DISTINCT(ProductID) when calling on top.

Thanks to you guys, I now understood why I got the ERROR, and believe that I'd be able to fix the problem using the code that I created earlier, BUT hopefully you guys can help me shorten/simplify the code for my better learning. All I want is to concatenate all fields: Specsheet, Manual, Demo, Driver, Utility, Firmware respectively and separate the data with a comma. Wait, now giving this question, I realize the code I had doesn't do what I want to do for every field while looping anyway.

Great! now I have to go back to review it again, still, please help me look into my request.

Thanks again!

 

[gmmastros]

Can you show a couple sample records from the tblSupports table, and what you WANT in to tblSupports2 table. This will help me to visualize what you are trying to do.

-George

"the screen with the little boxes in the window." - Moron

 

[cumap]

Oh... sure!

for ie:

I have productid 128 that has spec1 and spec2 saved seperately on 2 different support records... now I'd like to combine them both to support id X with the productid 128 and a string "spec1,spec2" in the Specsheet field... and that will do the same to others fields as well with the same productid#.

Please feel free asking me more question for the clarification.

 

[gmmastros]

Am I allowed to try and convince you that this is a bad idea?

By comma seperating your data, you are violating database normalization rules. I strongly encourage you to do a google search on 'Database Normalization'. Read the first couple articles that you find.

Think of this. Right now you want to store 3 different spec sheets. What happens 3 months from now when you want to store more? Do you make the column wider to accommodate it? You can, but where does it end?

Instead, the better design would be to have another table that stores the spec sheets for a product. In this table, you would have 2 columns (ProductId and SpecSheet). You would easily be able to link the 2 tables together because they both have the same value for ProductId. With this structure, you minimize your storage requirements, but you maximize your flexibility (by being able to store an unlimited number of spec sheets per product). You'll also be able to easily query the data to return all the spec sheets for a product, or do lookups on a single spec sheet.

Trust me. This is the better table design.

If you REALLY want to do it your way, I can help you with that. Just let me know.

-George

"the screen with the little boxes in the window." - Moron

 

[cumap]

you're right George!

I was about the tell you that I still want to pursuit the new technique... instead I decide to do what you suggest that is to create another table that contain supportID and productID for the better normalization. And for what reason I want to reform the database in the beginning, I will have to re-design the page for a user-friendly reason without the need to change the database anymore than I need to.

Cool!!! thanks for the reminding George!