| Author |
Topic |
|
mikus
Starting Member
9 Posts |
 Posted - 2006-03-22 : 11:08:46
|
| Can anybody help, please...I'm trying to import table [tbl1] from Access into MSSQL Database using SQL statement below (in double quote) out of VB.NET application. When executed I'm receiving error for SQL statment: 'Incorrect syntax near the keyword IN.'Dim SqlCommand As New System.Data.SqlClient.SqlCommand("SELECT * INTO [tbl1] FROM [tbl1] IN '' [ODBC;Driver={Microsoft Access Driver (*.mdb)};Dbq=\\Server1\C\db1.mdb;Uid=Admin;Pwd=;]", SqlConnection) |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-03-22 : 11:30:49
|
| It seems to be, u have mixed the data connection part and the SQL statementSQL Command should be finished with its end double quote and then may be after a comma or some such (u need to refer to ASP.Net syntax), u have to put the ODBC driver details |
 |
|
|
mikus
Starting Member
9 Posts |
Posted - 2006-03-22 : 11:49:06
|
| Thank you for prompt responseI'm using simmilar command for transfering table from MS SQL to Access using ODBC Driver and thaty is working fine:Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [table2] FROM [table2] IN '' [ODBC;Driver={SQL Server};Server=Server2;Database=" & ComboBox1.SelectedValue.ToString & ";Trusted_ Connection=yes];", AccessConn)This is not website and I don't use ASP.NET it's Windows application wrinten in VB.NET that use SQL Statements. |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-03-22 : 12:05:14
|
| Mikus,-- It may be ASP.Net or VB.net, but not SQL server (This is a SQL server forum)-- Change ur code in first one as .SqlClient.SqlCommand ==> .OleDb.OleDbCommand and see whether it works |
|
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-03-22 : 12:05:46
|
| Put square brackets [] around "IN", or eliminate the "IN" part. "IN" is a reserved keyword in SQL Server. |
|
|
|
mikus
Starting Member
9 Posts |
Posted - 2006-03-22 : 12:09:33
|
| Let me try.I'll respond as soon as it finish. |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-03-22 : 12:14:58
|
| nosepickerWhat he say is that one statement is working and the other is not, and IN is used in the same way in both placesMikus,Just Check whether ComboBox1.SelectedValue has a value and SqlConnection is active |
|
|
|
mikus
Starting Member
9 Posts |
Posted - 2006-03-22 : 12:30:38
|
| Srinika ur right second statement is working, while the first is not.ComboBox1 has a value and that is the part that is working.SqlConnection in non-working part is active - statements are executed only if connection is open.I've tried all the sugested solutions (Srinika, nosepicker) but without a luck. |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-03-22 : 15:11:59
|
| Mikus,Did u Change ur code in first one as .SqlClient.SqlCommand ==> .OleDb.OleDbCommand Also use "hardcoded value" for combobox instead of "control value"ieDatabase=" & ComboBox1.SelectedValue.ToString & ";==>Database=UrDBName; |
|
|
|
mikus
Starting Member
9 Posts |
Posted - 2006-03-22 : 15:27:49
|
| SrinikaYes I did.I have changed the .SqlClient.SqlCommand to .OleDb.OleDbCommand but the error remain the same: 'incorrect syntax near the keyword IN'I didn't do anything to the ComboBox since this belongs to the part that is working and I'm not using any combobox in part that is not working.Sorry for the confusion, but I added the second part only to show that the simmilar statement is running without any problem. |
|
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-03-22 : 15:41:13
|
quote:
nosepickerWhat he say is that one statement is working and the other is not, and IN is used in the same way in both places
But IN is not used in the same way, because in one instance he is going from Access to MSSQL (using the Access driver) and in the other instance he is going from MSSQL to Access (using the SQL Server driver). I was guessing that Access was allowing the use of IN in that way, but SQL Server was not. |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-03-22 : 15:43:26
|
quote:
Originally posted by mikus
I didn't do anything to the ComboBox since this belongs to the part that is working and I'm not using any combobox in part that is not working.Sorry for the confusion, but I added the second part only to show that the simmilar statement is running without any problem.
Hey mikus,I asked u to replace the comboBox.Selected... with a hardcoded value as a test.(Still I suspect the content of combo box or the connection is the culprit.Its good that u gave the working code as well, and it didn't confuse me. Instead I'm trying to get ur code working by changing parts of no working code with the one that is working. If u try changing part by part of non working code, with working code, u can find where the problem is!!Still, I strongly suggest u to Visit a .Net forum (ASP.Net or VB.Net - both would be same) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-03-22 : 15:45:22
|
| Access uses the JET database engine which lets you specify an "IN" clause when creating a new table. That syntax is NOT valid for SQL Server. If you want to create a table on a different server, you'd need to use linked servers or something like that. |
|
|
|
mikus
Starting Member
9 Posts |
Posted - 2006-03-22 : 16:06:43
|
| Thank you AllIs there a way to link servers programmatically since the output Access database must change depending on user preferences? |
|
|
|
mikus
Starting Member
9 Posts |
Posted - 2006-03-22 : 16:10:13
|
| sorry,I ment since output SQL Server database must change depending on user preferences? |
|
|
|
mikus
Starting Member
9 Posts |
Posted - 2006-03-22 : 17:17:37
|
| I think I've got this working through link servers as suggested.Thank all of you for your help. |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-03-22 : 17:29:21
|
| What I'd do is STConn.Open("Provider=SQLOLEDB;SERVER=ServerName;DATABASE=DBName - May Be the content of ur combo box-", "Username","Pswd") SqlStr = "Select ...." rs.Open(SqlStr, STConn) |
|
|
|
mikus
Starting Member
9 Posts |
Posted - 2006-03-22 : 17:59:26
|
| If that help anybody with similar problem, I have used OPENROWSET statement with Microsoft.jet.oledb.4.0 provider and that works and transfers data from Access to SQL Server. |
|
|
|
|
Select * into ... using ODBC - syntax error