• Advertisement
Sign in to follow this  

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

This topic is 4921 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hi all I want to duplicate a field in an Access database using an SQL sentence. What do I have to write in order to do that? Thanks for the help :-) Opps... Posted twice... Sorry :-)

Share this post


Link to post
Share on other sites
Advertisement
Update table Set field = 'value'
or
Update table Set field = 'value' Where condition

Share this post


Link to post
Share on other sites
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
Ok, I will try that thanks :)

Didn't work though :) Thanks anyway...

[Edited by - LinkOfTime on September 4, 2004 10:31:20 AM]

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.......)

Jay


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")

objRS.Close
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
Sign in to follow this  

  • Advertisement