Sign in to follow this  

[web] Hi all, I have an SQL + ASP question

Recommended Posts

Well... No, I wanted to duplicate an entire line, all of the fields in that line, and there are a lot of them. I was hoping I wouldn't have to copy each field seperately, but all of them together...
Well, hope there is some way to do it... Thanks for the help anyway... :-)

Share this post

Link to post
Share on other sites
Hi again
In the end, I used Access to create my SQL query to copy the fields, it came out long :). Anyway, it only works in Access and I don't know how to make it work in ASP's SQL too... Please help me fix it:

INSERT INTO UsersTable ( UserID, [Level], InitialType, SecondaryType, Life, LifeTotal, LifeTotalAdded, Attack, AttackTotal, AttackTotalAdded, Defense, DefenseTotal, DefenseTotalAdded, Speed, SpeedTotal, SpeedTotalAdded, AddPoints, Experience, ExperienceToNextLevel, [Attack(0)], [Attack(1)], [Attack(2)], [Attack(3)] )
SELECT UsersTable.UserID, UsersTable.Level, UsersTable.InitialType, UsersTable.SecondaryType, UsersTable.Life, UsersTable.LifeTotal, UsersTable.LifeTotalAdded, UsersTable.Attack, UsersTable.AttackTotal, UsersTable.AttackTotalAdded, UsersTable.Defense, UsersTable.DefenseTotal, UsersTable.DefenseTotalAdded, UsersTable.Speed, UsersTable.SpeedTotal, UsersTable.SpeedTotalAdded, UsersTable.AddPoints, UsersTable.Experience, UsersTable.ExperienceToNextLevel, UsersTable.[Attack(0)], UsersTable.[Attack(1)], UsersTable.[Attack(2)], UsersTable.[Attack(3)]
FROM UsersTable
WHERE ((([UsersTable]![UserID])="00000"))

Thanks in advance :)

Share this post

Link to post
Share on other sites
Did you know that you can access your stored queries from ASP's ADO functions?

Imagine you have a saved query "qMyHugeQuery", querying it is as simple as "SELECT * FROM qMyHugeQuery WHERE something = somethingelse"

This way, you'll never have to copy and paste queries into ASP, yet still keep their functionality.

You can even give them parameters too, if you need to.

Anyway, the reason for it not working is because you can't run two queries from a single execute call - which is annoying at the best of times. Instead, split the UPDATE / SELECT queries into separate parts and execute them individually.

Share this post

Link to post
Share on other sites
If you fancied using a parameterized query this code example may help, obviously though change the provider string to an access one. Note the adCmdStoredProc instead of adCmdText as its using parameters like a stored proc rather than including the query as the command text.

(Maybe you already know this stuff, but.......)


Const strConn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=MyDB;UID=sa;PWD=MyPassword"

Dim objCommand, objRS, objParam

Set objCommand = Server.CreateObject("ADODB.Command")
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorLocation = adUseClient

With objCommand
.CommandText = "MyDB.dbo.qry_LFT_Login"
.CommandType = adCmdStoredProc
.ActiveConnection = strConn
End With

Set objParam = objCommand.Parameters
objParam.Append objCommand.CreateParameter("@Username",adVarChar,adParamInput, 255)
objCommand("@Username") = strUsername
objParam.Append objCommand.CreateParameter("@Password",adVarChar,adParamInput, 255)
objCommand("@Password") = strPassword

objRS.Open objCommand,, adOpenForwardOnly, adLockReadOnly
Session("Username") = strUsername

objParam.Delete ("@Username")
objParam.Delete ("@Password")

Set objComand = Nothing

[Edited by - Jason Zelos on September 7, 2004 12:31:57 PM]

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this