asp and sql statement in sql server db

W

weiwei

HI
I am having a problem with asp and sql statement, the below script
works fine with access database at back, however, if I
put with sql server, I got an error states, do you guys have any idea,
please help me out, thanks in advance
wei


Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
syntax near '*'.
/test/jobpost/deljob.asp, line 24


'------------------------------------------------------------------------------------

<%
Set strConn = Server.CreateObject("ADODB.Connection")
strConn.Open "Driver={SQL Server};" & _
"Server=xxxxxxxxx;" & _
"Database=xxxxxxxxx;" & _
"Uid=xxxxxxxx;" & _
"Pwd=xxxxxxxx;"

%>

<% 'Set connCW = Server.CreateObject("ADODB.Connection") %>
<% 'connCW.Open ="Driver={Microsoft Access Driver (*.mdb)}; " &_
' "DBQ=" & Server.Mappath("job.mdb") %>

<%

num = Request.Form("num")

FOR I= 1 TO num
strID= Request.Form("ID" & I)

chrDel= Request.Form("del" & I)
IF chrDel <> "" THEN

strSQL = "DELETE * FROM position " &_
"WHERE ID = " & strID
SET rs = strConn.execute(strSQL)
End If
NEXT

strSQL = "SELECT * FROM position"
SET rsd = strConn.execute(strSQL)

If rsd.EOF Then
response.write("You have no transaction.")
Else

%>

<% End If %>
 
S

Scott McNair

(e-mail address removed) (weiwei) wrote in @posting.google.com:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
syntax near '*'.
/test/jobpost/deljob.asp, line 24

Are you supplying a default database anywhere? You either need to set a
default database or refer to your database in the SQL statement as

strSQL = "SELECT * FROM MyDatabaseName.dbo.MyTableName WHERE Foo =
'Bar'"

To specify the default database, set your connection string to something
like:

strConn.Open "Provider=SQLOLEDB; Initial Catalog=[your database]; Data
Source=[your ip]; uid=[your login];pwd=[your password];"

If you do that, you should be able to refer to your table without having
to give a database name every time.
 
J

Jeff Cochran

HI
I am having a problem with asp and sql statement, the below script
works fine with access database at back, however, if I
put with sql server, I got an error states, do you guys have any idea,
please help me out, thanks in advance
wei


Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
syntax near '*'.
/test/jobpost/deljob.asp, line 24

Assuming line 24 is where you execute strSQL, do a response.write of
strSQL and see waht you're actually using as a query when you execute
it.

Jeff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,733
Messages
2,569,439
Members
44,829
Latest member
PIXThurman

Latest Threads

Top