Select statement help.

[rjn2001]

Yes People, I am the dumbest person you'll get on this forum today, but you know, it was Christmas lsat week!

I could do with some help with my select statement, I am wanting to submit to a MS Access Db, where I have user table, Job table, Project table etc.

I am using quite stringent securtiy, and login etc too, so the data should be submitted to the table and then be able to be drawn back out again too.

Can anyone help?

Let me know what you need to assist in my squandering!

 

[adamsoderqvist]

Hi, I don't know if this is what U are after, but a simple select syntax is:

****************
'Define an SQL-string
Dim strSQL

strSQL = "SELCT * FROM tblUser"

Response.Write strSQL

****************

This will return and print ALL users (by using the *) from your user table.

If this doesn't help, be more specific on what you need...

/A

 

[rjn2001]

This is the text I have before the header,....

'Make sure this page is not cached
Response.Expires = -1
Response.ExpiresAbsolute = Now() - 2
Response.AddHeader "pragma","no-cache"
Response.AddHeader "cache-control","private"
Response.CacheControl = "No-Store"

Dim strSQL 'Database query sring
Dim strUserName 'Holds the user name
Dim strPassword 'Holds Password
Dim strJobDatesStart 'Holds Job Start Date
Dim strJobDatesFinish 'Holds Job End Date
Dim strJobDescription 'Holds Job Description
Dim strJobRole 'Holds Job Role

strUserName = Request.Form("UserID")
strPassword = Request.Form("Password")

Dim rand 'Holds Encrypt Key of Password
Dim objMD5 'Holds Object MD5

Set objMD5 = New MD5
objMD5.Text = strPassword
rand = objMD5.HEXMD5


'Create a recordset object
Set rsCheckUser = Server.CreateObject("ADODB.Recordset")

'Initalise the strSQL variable with an SQL statement to query the database
strSQL = "Select * From tblUsers WHERE tblUsers.UserID ='" & strUserName & "' AND tblUsers.Password = '" & rand &"'"

'Query the database
rsCheckUser.Open strSQL, strCon
%>

I want to change this to:-

a) Submit data to tblJob, tblProject etc
b) Retrive that data from the db so it can be updated etc.

* the form that I am using for submitting the data can be found at

http://217.41.37.226:3086/job.asp

so, (if you have looked at it), you will see it is dynamic!, and therefore the data retrival needs to be good in order for it to work.

I sound vague, I know, I apolgise!, I am just so confused with it all now, I promise never to do an ASP assignment in my life!

 

[adamsoderqvist]

OK.. From what I can understand, you want

a) Add data to the tables Job and Project, and
b) Be able to update that data on specific occasions

A simple way of adding (a) is:

**************CODE**************

'Declare var's for - and open - database connection
Dim objConn, sConnection
Set objConn = Server.CreateObject("ADODB.Connection")
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("../_public/MyDB.mdb") & "; Persist Security Info=False"

objConn.Open(sConnection)

'Declare variables for the new post...
Dim strJob, strFromMonth, strToMonth, strMyTasks, strMyRole
strJob = Request("Job")
strFromMonth = Request("FromMonth")
strToMonth = Request("ToMonth")
strMyTasks = Request("MyTasks")
strMyRole = Request("MyRole")

'Declare recordset object...
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")

'Open the database!
objRS.Open "tblJob", objConn, , adLockOptimistic

'Perform add action...
objRS.AddNew
objRS("Job") = strJob
objRS("FromMonth") = strFromMonth
objRS("ToMonth") = strToMoth
objRS("MyTasks") = strMyTasks
objRS("MyRole") = strMyRole
objRS.Update

'Close objects, and return to add article area...
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

***************END CODE*************

And a simple way of updating (b) is:

(I'm not doing the entire coding here, just the major principles)

1. Open the database
2. strSQL = "Select * From tblJob"
3. Load the values into textboxes (in a form):
<input type=text name=Job value=<%=objRS("Job")%>
<input type=text name=FromMonth value=<%=objRS("FromMonth")%> .. etc. until all values are loaded into textboxes!
4. When all values are loaded from tblJob, have a submit button in the form that re-enters THAT information into the tblJob again. Violá - you have updated the info!

Something like this:

Dim strSQL
strSQL = "SELECT * FROM tblJob"

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn, , adLockOptimistic

'Begin update session..
objRS("Job") = strJob
objRS("FromMonth") = strFromMonth
objRS("ToMonth") = strToMonth
objRS("MyTasks") = strMyTasks
objRS("MyRole") = strMyRole
objRS.Update

'Close objects...
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

Hope this helps a bit..