473,325 Members | 2,774 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

asp and sql statement in sql server db

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 %>
Jul 19 '05 #1
4 1318
DELETE FROM ...
Not
DELETE * FROM ...

--
Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com
"weiwei" <We*******@slps.org> wrote in message
news:d3**************************@posting.google.c om...
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 %>

Jul 19 '05 #2
We*******@slps.org (weiwei) wrote in news:d32b0478.0409220627.690e43c1
@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.
Jul 19 '05 #3
On 22 Sep 2004 07:27:33 -0700, We*******@slps.org (weiwei) wrote:
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
'------------------------------------------------------------------------------------

<%
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 %>


Jul 19 '05 #4
One last thing no one mentioned stop using ODBC/DSN.

Check out the following links.

http://www.aspfaq.com/show.asp?id=2126

http://msdn.microsoft.com/library/de...components.asp

weiwei wrote:
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


Jul 19 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Ben | last post by:
I have two 'Do While Not' statements, that are getting information from the same recordset. If I comment out the first one I can get the results for the second one, and vice-versa. Why is this...
1
by: dmiller23462 | last post by:
Hey guys.... I put an error-handling in my page and have it posted at the complete end of the code, see below(when people were putting in 's I was getting the delimiter errors). Great, I...
8
by: pb648174 | last post by:
I have a single update statement that updates the same column multiple times in the same update statement. Basically i have a column that looks like .1.2.3.4. which are id references that need to...
6
by: HeadScratcher | last post by:
I am trying to speed up my update statements by removing inner select statements. Example: update orders set shipname = (select contactName from customers where customerid = orders.customerID)...
10
by: Philip Sherman | last post by:
SELECT columns FROM t1 JOIN t2
9
by: Michael | last post by:
Hi all, I would like to get people's opinion about executing SQL statements in C# (or any other .NET language really). I used to create my SQL statement by building a string and replacing single...
6
by: tshad | last post by:
In my User control, I tried to do this: *************************************************************************** <Script runat="server"> Public ClientName As String = "<!-- #include file =...
2
by: travhale | last post by:
in a new project using .net 2005, c#. getting err message "Update requires a valid UpdateCommand when passed DataRow collection with modified rows." source RDBMS is oracle 8i. I add a new...
8
by: nano2k | last post by:
Hi Shortly, I keep invoices in a table. Occasionally, someone will fire the execution of a stored procedure (SP) that performs several UPDATEs against (potentially) all invoices OLDER than a...
1
by: codedhacker | last post by:
I have a piece of code that uses the db-library with sql server 2000/2005 and runs the following delete statement: DELETE FROM TABLE1 WHERE COL1 IN( 'Some Val1' ) AND COL2 IN( 'Some Val2' )...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.