Nugs
asked on
Placing the value of a DB table field in a variable.
Hey there guys,
I'm playing with my first ASP.NET site here so excuse my ignorance. What I need to do is simple but I would like some code to get me going if possible.
Basically I have a table in the DB called IDNums. IDNums has only one record and numerous fields all serving the same purpose. One of the fields holds the sites last member number that was assigned to the user who registers last.
What I need to do on the page where the user registers is take this number from the DB and increment it by 1, then place it in a hidden form field where it can be inserted with the rest of there data.
Basicly just need to post the DB info into a variable that i can use.
Thanks
Nugs
I'm playing with my first ASP.NET site here so excuse my ignorance. What I need to do is simple but I would like some code to get me going if possible.
Basically I have a table in the DB called IDNums. IDNums has only one record and numerous fields all serving the same purpose. One of the fields holds the sites last member number that was assigned to the user who registers last.
What I need to do on the page where the user registers is take this number from the DB and increment it by 1, then place it in a hidden form field where it can be inserted with the rest of there data.
Basicly just need to post the DB info into a variable that i can use.
Thanks
Nugs
ASKER
acperkins, sorry i tend to loose track of these things...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
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
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.
ASKER
>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="DreamweaverCtrl
<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.Confi
DatabaseType='<%# System.Configuration.Confi
Expression='<%# Request.Form("MM_insert") = "form1" %>'
CreateDataSet="false"
SuccessURL='<%# "newlogininfo.aspx?MemberN
Debug="true"
><Parameters>
<Parameter Name="@Address1" Value='<%# IIf((Request.Form("Address
<Parameter Name="@Address2" Value='<%# IIf((Request.Form("Address
<Parameter Name="@CellPhn" Value='<%# IIf((Request.Form("CellPhn
<Parameter Name="@City" Value='<%# IIf((Request.Form("City") <> Nothing), Request.Form("City"), "") %>' Type="WChar" />
<Parameter Name="@Company" Value='<%# IIf((Request.Form("Company
<Parameter Name="@Country" Value='<%# IIf((Request.Form("Country
<Parameter Name="@DaytimePhn" Value='<%# IIf((Request.Form("Daytime
<Parameter Name="@EMail" Value='<%# IIf((Request.Form("usremai
<Parameter Name="@EveningPhn" Value='<%# IIf((Request.Form("Evening
<Parameter Name="@FAX" Value='<%# IIf((Request.Form("FAX") <> Nothing), Request.Form("FAX"), "") %>' Type="WChar" />
<Parameter Name="@FirstName" Value='<%# IIf((Request.Form("FirstNa
<Parameter Name="@LastName" Value='<%# IIf((Request.Form("LastNam
<Parameter Name="@USState" Value='<%# IIf((Request.Form("USState
<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.Confi
DatabaseType='<%# System.Configuration.Confi
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="DreamweaverCtrl
runat="server"
CommandText='<%# "INSERT INTO LoginInformation (PassHint, Password, UserID) VALUES (?, ?, ?)" %>'
ConnectionString='<%# System.Configuration.Confi
DatabaseType='<%# System.Configuration.Confi
Expression='<%# Request.Form("MM_insert") = "NewUsrLogin" %>'
CreateDataSet="false"
SuccessURL='<%# "ClientCenter" %>'
Debug="true"
><Parameters>
<Parameter Name="@PassHint" Value='<%# IIf((Request.Form("PassHin
<Parameter Name="@Password" Value='<%# IIf((Request.Form("UsrPass
<Parameter Name="@UserID" Value='<%# IIf((Request.Form("LoginNa
</Parameters>
</MM:Insert>
<MM:DataSet
id="NewLogin"
runat="Server"
IsStoredProcedure="false"
ConnectionString='<%# System.Configuration.Confi
DatabaseType='<%# System.Configuration.Confi
CommandText='<%# "SELECT MemberNum FROM ClientInfo WHERE MemberNum = ?" %>'
Debug="true"
>
<Parameters>
<Parameter Name="@MemberNum" Value='<%# IIf((Request.QueryString("
</Parameters>
</MM:DataSet>
<MM:PageBind runat="server" PostBackBind="true" />
--------------------------
Thats is my situtaion and where i am at with it...
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="DreamweaverCtrl
<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.Confi
DatabaseType='<%# System.Configuration.Confi
Expression='<%# Request.Form("MM_insert") = "form1" %>'
CreateDataSet="false"
SuccessURL='<%# "newlogininfo.aspx?MemberN
Debug="true"
><Parameters>
<Parameter Name="@Address1" Value='<%# IIf((Request.Form("Address
<Parameter Name="@Address2" Value='<%# IIf((Request.Form("Address
<Parameter Name="@CellPhn" Value='<%# IIf((Request.Form("CellPhn
<Parameter Name="@City" Value='<%# IIf((Request.Form("City") <> Nothing), Request.Form("City"), "") %>' Type="WChar" />
<Parameter Name="@Company" Value='<%# IIf((Request.Form("Company
<Parameter Name="@Country" Value='<%# IIf((Request.Form("Country
<Parameter Name="@DaytimePhn" Value='<%# IIf((Request.Form("Daytime
<Parameter Name="@EMail" Value='<%# IIf((Request.Form("usremai
<Parameter Name="@EveningPhn" Value='<%# IIf((Request.Form("Evening
<Parameter Name="@FAX" Value='<%# IIf((Request.Form("FAX") <> Nothing), Request.Form("FAX"), "") %>' Type="WChar" />
<Parameter Name="@FirstName" Value='<%# IIf((Request.Form("FirstNa
<Parameter Name="@LastName" Value='<%# IIf((Request.Form("LastNam
<Parameter Name="@USState" Value='<%# IIf((Request.Form("USState
<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.Confi
DatabaseType='<%# System.Configuration.Confi
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="DreamweaverCtrl
runat="server"
CommandText='<%# "INSERT INTO LoginInformation (PassHint, Password, UserID) VALUES (?, ?, ?)" %>'
ConnectionString='<%# System.Configuration.Confi
DatabaseType='<%# System.Configuration.Confi
Expression='<%# Request.Form("MM_insert") = "NewUsrLogin" %>'
CreateDataSet="false"
SuccessURL='<%# "ClientCenter" %>'
Debug="true"
><Parameters>
<Parameter Name="@PassHint" Value='<%# IIf((Request.Form("PassHin
<Parameter Name="@Password" Value='<%# IIf((Request.Form("UsrPass
<Parameter Name="@UserID" Value='<%# IIf((Request.Form("LoginNa
</Parameters>
</MM:Insert>
<MM:DataSet
id="NewLogin"
runat="Server"
IsStoredProcedure="false"
ConnectionString='<%# System.Configuration.Confi
DatabaseType='<%# System.Configuration.Confi
CommandText='<%# "SELECT MemberNum FROM ClientInfo WHERE MemberNum = ?" %>'
Debug="true"
>
<Parameters>
<Parameter Name="@MemberNum" Value='<%# IIf((Request.QueryString("
</Parameters>
</MM:DataSet>
<MM:PageBind runat="server" PostBackBind="true" />
--------------------------
Thats is my situtaion and where i am at with it...
ASKER
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.OleDbExc
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at DreamweaverCtrls.DataSet.D
____>
System.Data.OleDb.OleDbExc
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at DreamweaverCtrls.DataSet.D
>>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.
I suggest you post a new question, asking this specifically, this way you will have maximum exposure and more response.
ASKER
thought i did but, yeah ok.......
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