Solved

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

Posted on 2004-04-03
11
284 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
0
Comment
Question by:Nugs
  • 6
  • 5
11 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10754355
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
0
 
LVL 2

Author Comment

by:Nugs
ID: 10754589
acperkins, sorry i tend to loose track of these things...
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 125 total points
ID: 10754613
>>What I need to do on the page where the user registers is take this number from the DB and increment it by 1<<
Save yourself the headache and use an IDENTITY column in SQL Server or an Auto-Increment clumn in MS Access.  This will automatically increment, every time a new row is added.
0
 
LVL 2

Author Comment

by:Nugs
ID: 10754654
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
0
 
LVL 2

Author Comment

by:Nugs
ID: 10754700
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.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10754703
>>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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10754709
>>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.

0
 
LVL 2

Author Comment

by:Nugs
ID: 10755064
>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...
0
 
LVL 2

Author Comment

by:Nugs
ID: 10755089
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()
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10757484
>>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.
0
 
LVL 2

Author Comment

by:Nugs
ID: 10759017
thought i did but, yeah ok.......
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now