Placing the value of a DB table field in a variable.

Please maintain your old open questions:

1 01/13/2003 50 IIS and .NET services don't seem to be w...  Open Internet Information Server
2 02/26/2004 30 .Net Hosting Companies  Open Web Hosting
3 09/17/2003 50 Need some fonts, can anyone help?  Open Fonts

acperkins, sorry i tend to loose track of these things...

Yes you are right this is how I could do this but I came into problems when working with the DB, I wanted this column in the MS Access DB to have a one to many relationship with other tables. The data types needs to be the same to do this and the table it relates to has many of the same numbers, one too many.

I though that the way around this was to create a separate table that holds these values and use it to generate the users numbers and assign each user a unique number each time a account is created and still have my relationships in the DB.

You right though, it is a round about way to have best of both worlds and I guess I could just remove the relationship in the DB and make the id column AutoNumber. I was only thinking of integrity and ease of use when working directly with the DB.

There are other applications for my original question though. I still would like to know how a value from a DB can be placed into a variable I can use in my code. My problem is solved though so thank you for the kick in the butt to bring me back to my senses.

Nugs

PS: I also wanted to capture this number so that it can be inserted into more than one table at once. If it is AutoNumber i don't have a way of knowing what the number is until the record is created.

>>You right though, it is a round about way to have best of both worlds and I guess I could just remove the relationship in the DB and make the id column AutoNumber.<<
There is no reason to lose the referential integrity when using a table with a column defined as autonumber.  I am not fully understanding your situation, but take a look at the Northwind sample database and you will see that the Order ID in the Orders table is defined as Autonumber and there is a relationship with the Order Details table through that column.

>>I still would like to know how a value from a DB can be placed into a variable I can use in my code.<<
This is handled very simply with ADO.  At the simplest level (there are better ways of doing this) somehting like this should work (untested):

<%
Dim rs
Dim MyVar

Set rs = Server.CreateObject("ADODB.Recordset")
With rs
   .Source = "Select CustomerID From Orders Where OrderID = 10272"
   .ActiveConnection = "your connection string goes here"
   .CursorType = 0          ' adOpenForwardOnly
   .LockType = 1           ' adLockReadOnly
   .Open ,,,, 1        ' adCmdText
   MyVar = .Fields("CustomerID"). Value
   .Close  
End With
Set rs = Nothing

>>If it is AutoNumber i don't have a way of knowing what the number is until the record is created. <<
This really depends on the Provider you are using, but it is typically pretty easy to do without re-querying the database.

>This really depends on the Provider you are using, but it is typically pretty easy to do without re-querying the database.

I would love to know how because I am still having trouble.

>I am not fully understanding your situation,

Well all I am trying to do is have a user input there contact information and there desired login information for there account, both held is separate tables in the DB. At first I wanted to insert this information from one single form to both tables. And at the same time generate a user number that can be placed in both tables to "connect" the data. Well to avoid the headache that will for sure come along with trying to do that I opted to split the registration process into two forms, the first where I gather there user info, the second there login info for there account. Where I am now is attempting to pass the member number that only gets generated once the contact info is inserted onto the second form so the same user number can be placed in the table holding there login info.

It passes the first record in the table and not the record that was just created.

The first forms code looks like so:
----------------------------------------
<%@ Page Explicit="True" Language="VB" Debug="True" %>
<%@ Register TagPrefix="MM" Namespace="DreamweaverCtrls" Assembly="DreamweaverCtrls,version=1.0.0.0,publicKeyToken=836f606ede05d46a,culture=neutral" %>
<MM:Insert
runat="server"
CommandText='<%# "INSERT INTO ClientInfo (Address1, Address2, CellPhn, City, Company, Country, DaytimePhn, EMail, EveningPhn, FAX, FirstName, LastName, USState, ZipCode) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" %>'
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_eOnConn") %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_DATABASETYPE_eOnConn") %>'
Expression='<%# Request.Form("MM_insert") = "form1" %>'
CreateDataSet="false"
SuccessURL='<%# "newlogininfo.aspx?MemberNum=" +  NewUser.FieldValue("MemberNum", Container) %>'
Debug="true"
><Parameters>
  <Parameter Name="@Address1" Value='<%# IIf((Request.Form("Address1") <> Nothing), Request.Form("Address1"), "") %>' Type="WChar" />  
  <Parameter Name="@Address2" Value='<%# IIf((Request.Form("Address2") <> Nothing), Request.Form("Address2"), "") %>' Type="WChar" />  
  <Parameter Name="@CellPhn" Value='<%# IIf((Request.Form("CellPhn") <> Nothing), Request.Form("CellPhn"), "") %>' Type="WChar" />  
  <Parameter Name="@City" Value='<%# IIf((Request.Form("City") <> Nothing), Request.Form("City"), "") %>' Type="WChar" />  
  <Parameter Name="@Company" Value='<%# IIf((Request.Form("Company") <> Nothing), Request.Form("Company"), "") %>' Type="WChar" />  
  <Parameter Name="@Country" Value='<%# IIf((Request.Form("Country") <> Nothing), Request.Form("Country"), "") %>' Type="WChar" />  
  <Parameter Name="@DaytimePhn" Value='<%# IIf((Request.Form("DaytimePhn") <> Nothing), Request.Form("DaytimePhn"), "") %>' Type="WChar" />  
  <Parameter Name="@EMail" Value='<%# IIf((Request.Form("usremail") <> Nothing), Request.Form("usremail"), "") %>' Type="WChar" />  
  <Parameter Name="@EveningPhn" Value='<%# IIf((Request.Form("EveningPhn") <> Nothing), Request.Form("EveningPhn"), "") %>' Type="WChar" />  
  <Parameter Name="@FAX" Value='<%# IIf((Request.Form("FAX") <> Nothing), Request.Form("FAX"), "") %>' Type="WChar" />  
  <Parameter Name="@FirstName" Value='<%# IIf((Request.Form("FirstName") <> Nothing), Request.Form("FirstName"), "") %>' Type="WChar" />  
  <Parameter Name="@LastName" Value='<%# IIf((Request.Form("LastName") <> Nothing), Request.Form("LastName"), "") %>' Type="WChar" />  
  <Parameter Name="@USState" Value='<%# IIf((Request.Form("USState") <> Nothing), Request.Form("USState"), "") %>' Type="WChar" />  
  <Parameter Name="@ZipCode" Value='<%# IIf((Request.Form("Zip") <> Nothing), Request.Form("Zip"), "") %>' Type="WChar" />
</Parameters>
</MM:Insert>
<MM:DataSet
id="NewUser"
runat="Server"
IsStoredProcedure="false"
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_eOnConn") %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_DATABASETYPE_eOnConn") %>'
CommandText='<%# "SELECT MemberNum FROM ClientInfo" %>'
Debug="true"
></MM:DataSet>
<MM:PageBind runat="server" PostBackBind="true" />
--------------------------------------------

The next forms code looks like so:
--------------------------------------------
<%@ Page Explicit="True" Language="VB" Debug="True" %>
<%@ Register TagPrefix="MM" Namespace="DreamweaverCtrls" Assembly="DreamweaverCtrls,version=1.0.0.0,publicKeyToken=836f606ede05d46a,culture=neutral" %><MM:Insert
runat="server"
CommandText='<%# "INSERT INTO LoginInformation (PassHint, Password, UserID) VALUES (?, ?, ?)" %>'
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_eOnConn") %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_DATABASETYPE_eOnConn") %>'
Expression='<%# Request.Form("MM_insert") = "NewUsrLogin" %>'
CreateDataSet="false"
SuccessURL='<%# "ClientCenter" %>'
Debug="true"
><Parameters>
  <Parameter Name="@PassHint" Value='<%# IIf((Request.Form("PassHint") <> Nothing), Request.Form("PassHint"), "") %>' Type="WChar" />  
  <Parameter Name="@Password" Value='<%# IIf((Request.Form("UsrPass") <> Nothing), Request.Form("UsrPass"), "") %>' Type="WChar" />  
  <Parameter Name="@UserID" Value='<%# IIf((Request.Form("LoginName") <> Nothing), Request.Form("LoginName"), "") %>' Type="WChar" />
</Parameters>
</MM:Insert>
<MM:DataSet
id="NewLogin"
runat="Server"
IsStoredProcedure="false"
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_eOnConn") %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_DATABASETYPE_eOnConn") %>'
CommandText='<%# "SELECT MemberNum FROM ClientInfo WHERE MemberNum = ?" %>'
Debug="true"
>
  <Parameters>
    <Parameter  Name="@MemberNum"  Value='<%# IIf((Request.QueryString("MemberNum") <> Nothing), Request.QueryString("MemberNum"), "") %>'  Type="Integer"   />
  </Parameters>
</MM:DataSet>
<MM:PageBind runat="server" PostBackBind="true" />
-----------------------------------------------

Thats is my situtaion and where i am at with it...

Oh and the first form inserts properly, the second gives a error. i suspect because it is always trying to insert member number 1 (The first record in the table instead of the one just created) which already exists.

____>

System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at DreamweaverCtrls.DataSet.DoInit()

>>I would love to know how because I am still having trouble. <<
I suggest you post a new question, asking this specifically, this way you will have maximum exposure and more response.

thought i did but, yeah ok.......