We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Nugs
Nugs asked
on
Medium Priority
313 Views
Last Modified: 2012-08-13
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2012

Commented:
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

Author

Commented:
acperkins, sorry i tend to loose track of these things...
CERTIFIED EXPERT
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Author

Commented:
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.
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>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
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>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.

Author

Commented:
>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...

Author

Commented:
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()
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>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.

Author

Commented:
thought i did but, yeah ok.......
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.