Really sorry, I'm a bit lost and keep going around in circles - I've been on tons of forums and tutorials and yet, it seems like it's impossible to find the answer I need.
I want the ASPX page, which has textboxes, dropdowns, checkboxes, etc, to find its way into my database table using a button. Simple?
I am already using a connection to the Database itself - but not this particular table yet - I have no problem retrieving information from other tables but am yet to successfully write anything from a form into a table
Please help!
I've tried to break it down below, I hope it makes sense. If you need any more information, let me know. I'm using Visual Web Developer 2008 and have a 2005/2008 SQL Database.
I'm not asking you to write it, just what, how and where do I start with this? Thanks
‘ The first thing I’m stuck on is, behind this button that creates the record in the table called dbo.Job, how do I establish my connection? I already have other connections for my drop-downs but, this time I want to write to it. The connection is:
On the click of the button, I want it to do the following:
INSERT INTO dbo.Job
( JobDesc
, JobStatusID
, CompanyID
, LocationID
, HandlerID
, ClientID
, DepartmentID
, ClContactID
, CreatedDate
, StartDate
, RequestedDate
)
VALUES
( @JobName
, @StatusID
, @CompID
, @LocID
, @HandID
, @ClientID
, @DeptID
, @CL_CtID
, @CreDate
, @StaDate
, @ReqDate
)
‘ My web form is dynamically populated by different tables in the same connection and the ID value is stored but the text is displayed. So, I imagine I want something like this?
cmd.Parameters.AddWithValue("@JobName” , JobTitle.Value)
cmd.Parameters.AddWithValue("@StatusID” , JobStatus.Value)
cmd.Parameters.AddWithValue("@CompID" , CompanyName.Value)
cmd.Parameters.AddWithValue("@LocID" , CompanyLocation.Value)
cmd.Parameters.AddWithValue("@HandID" , StudioContact.Value)
cmd.Parameters.AddWithValue("@ClientID" , Client.Value)
cmd.Parameters.AddWithValue("@DeptID" , Department.Value)
cmd.Parameters.AddWithValue("@CL_CtID" , CClientContact.Value
cmd.Parameters.AddWithValue("@CreDate" , GetDate())
cmd.Parameters.AddWithValue("@StaDate" , StartDate.Value)
cmd.Parameters.AddWithValue("@ReqDate" , RequiredDate.Value)
‘ The one I’m not sure about is putting Creation Date in using GetDate() unless I can get the database to do that automatically in that column?
‘
‘ Then I need a variable to = SELECT IDENTITY so I know the JobID number. This is so I can now create multiple records in a related table and use the variable in the creation process to relate it back to the first table I created records in.
‘
‘ Then when that’s done, I want to work out how many selections there are in a Checkbox list called PrimaryColours.
‘ Don’t know what the command is but I want
Variable “Primary” to equal the ‘number of selections made in Checkbox ‘Primary Colours’
Then:
For I = 1 to Primary
Insert into different table called ‘ProductSpec’
INSERT INTO ProductSpec
(
JobID
, ColourSetting
)
VALUES
(
@JobRef
, @ColourSpec
)
cmd.Parameters.AddWithValue("@ColourSpec” , PrimaryColours.item(I).Selected)
cmd.Parameters.AddWithValue("@ColourSpec” , PrimaryColours.item(I).Selected)
NEXT
‘ Loops until it creates all the related colour spec records
Not asking for much are you? ;) First off take a look at the button1_OnClick method here:
http://forums.asp.net/t/1193643.aspx. Secondly to get the scope identity out do something like this:
SqlParameter parmOut = new SqlParameter("@ID", SqlDbType.Int);
parmOut.Direction = ParameterDirection.Output;
cmd.Parameters.AddWithValue(parmOut);
//after you insert run this code with i being the position of the given parameter
int id = (int)cmd.Parameters[i].Value;
As far as the getdate(), I would set that as the default value for that column in the db itself, therefore you don't have to mess with it.
If my reply helps answer your question then please mark as answer.
Yeah, I knew there was a lot in there, huh! Thanks for your help with this, I've checked out that link
- is there any problem mixing C# with VB in the same page? Only, I've done most of the other stuff in VB because it's what I've know for ages and know it doesn't make much difference in performance.
I will get my head around the rest in a little bit - it's 20:13 in the evening and I've cracked open my second beer, it's becoming difficult to concentrate :)
I don't believe that you can use C# and VB in the same page. I know you can in the same project though. It shouldn't be that hard to change that over to VB, give it a try and if you run into any problems let me know. Crack another open for me!
If my reply helps answer your question then please mark as answer.
Thank you all for your help so far. That link, looks great but, I am trying to get my head around it and convert it to VB, as the rest of my page and page code is in VB.
http://forums.asp.net/t/1193643.aspx
First thing I don't understand is the connection string - the means of connecting to my database. The thing I don't get is, I'm already connected to my database using, from the top of my head, <%connectionstring:connectionstring%>. I developed the SQL
database inside Visual Wed Developer with SQL Express running in the background. Whenever I create a new dropdown to the page, it knows to use the connectionstring and then allows me to select a table and enter a query, value and text for the dropdown.
So, I don't think I need all that stuff if it is already established? Unless, there is some difference in my connectionstring if it is to read AND write information?
The first hurdle I want to do is, using a button, create a database record in one table called dbo.Job. I know there is also some scripting to do to get the IDENTITY, and then work out how many additional records in a related table I need to make but, right
now, I'm still at the point where I haven't been able to create a single record from my ASPX page.
Let's say for not I have, on the ASPX page.
TextBox - called 'JobName' The TEXT of this to go into JobDescription in the dbo.Job table
DropDown - called 'JobType' The VALUE of this to go into JobTypeID in the dbo.Job table
The only other bit I'd like to know is, if I have a label on my aspx page called JobNumber, how do I do a SELECT IDENTITY of the dbo.Job table and make the label display the Job Number?
Sorry I ask a lot, hopefully, this is simple stuff, I read up so much I end up lost in the process.
Thanks Yogesh, I appreciate the change to the code. Is that deemed as a Function which STARTS at the top of the button script and ends with the line 7?
Also, any ideas about using the existing connection I have for retrieving information from the database?
I have for retrieving information from the database?
use below code to fetch data..
SqlConnection connection = new SqlConnection("server=servername;Initial Catalog=database;User Id=sa;Password=pwd;");
//server=servername;Initial Catalog=database;User Id=sa;Password=pwd;
//is your connection string
//better to place this in web.config file
SqlCommand command = new SqlCommand("Select * from TableName", connection);
//If you want to add parameter than use like belw
//command.Parameters.Add("@id", SqlDbType.VarChar).Value = textbox.text;
SqlDataAdapter adp = new SqlDataAdapter(command);
DataTable dt = new DataTable();
adp.Fill(dt);
--
and
mattlightbourn
Also, any ideas about using the existing connection
I am not getting this one....What do you mean by existing connection?
Thanks again for your help . What I meant was, I have five dropdowns and for each, I don't need to setup a connection, only a select statement with select parameters
mattlightbou...
Member
3 Points
53 Posts
Inserting data into SQL Tables from ASP Form
Dec 17, 2009 06:27 PM|LINK
Hi all,
Really sorry, I'm a bit lost and keep going around in circles - I've been on tons of forums and tutorials and yet, it seems like it's impossible to find the answer I need.
I want the ASPX page, which has textboxes, dropdowns, checkboxes, etc, to find its way into my database table using a button. Simple?
I am already using a connection to the Database itself - but not this particular table yet - I have no problem retrieving information from other tables but am yet to successfully write anything from a form into a table
Please help!
I've tried to break it down below, I hope it makes sense. If you need any more information, let me know. I'm using Visual Web Developer 2008 and have a 2005/2008 SQL Database.
I'm not asking you to write it, just what, how and where do I start with this? Thanks
‘ The first thing I’m stuck on is, behind this button that creates the record in the table called dbo.Job, how do I establish my connection? I already have other connections for my drop-downs but, this time I want to write to it. The connection is:
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
On the click of the button, I want it to do the following:
INSERT INTO dbo.Job ( JobDesc , JobStatusID , CompanyID , LocationID , HandlerID , ClientID , DepartmentID , ClContactID , CreatedDate , StartDate , RequestedDate ) VALUES ( @JobName , @StatusID , @CompID , @LocID , @HandID , @ClientID , @DeptID , @CL_CtID , @CreDate , @StaDate , @ReqDate ) ‘ My web form is dynamically populated by different tables in the same connection and the ID value is stored but the text is displayed. So, I imagine I want something like this? cmd.Parameters.AddWithValue("@JobName” , JobTitle.Value) cmd.Parameters.AddWithValue("@StatusID” , JobStatus.Value) cmd.Parameters.AddWithValue("@CompID" , CompanyName.Value) cmd.Parameters.AddWithValue("@LocID" , CompanyLocation.Value) cmd.Parameters.AddWithValue("@HandID" , StudioContact.Value) cmd.Parameters.AddWithValue("@ClientID" , Client.Value) cmd.Parameters.AddWithValue("@DeptID" , Department.Value) cmd.Parameters.AddWithValue("@CL_CtID" , CClientContact.Value cmd.Parameters.AddWithValue("@CreDate" , GetDate()) cmd.Parameters.AddWithValue("@StaDate" , StartDate.Value) cmd.Parameters.AddWithValue("@ReqDate" , RequiredDate.Value) ‘ The one I’m not sure about is putting Creation Date in using GetDate() unless I can get the database to do that automatically in that column? ‘ ‘ Then I need a variable to = SELECT IDENTITY so I know the JobID number. This is so I can now create multiple records in a related table and use the variable in the creation process to relate it back to the first table I created records in. ‘ ‘ Then when that’s done, I want to work out how many selections there are in a Checkbox list called PrimaryColours. ‘ Don’t know what the command is but I want Variable “Primary” to equal the ‘number of selections made in Checkbox ‘Primary Colours’ Then: For I = 1 to Primary Insert into different table called ‘ProductSpec’ INSERT INTO ProductSpec ( JobID , ColourSetting ) VALUES ( @JobRef , @ColourSpec ) cmd.Parameters.AddWithValue("@ColourSpec” , PrimaryColours.item(I).Selected) cmd.Parameters.AddWithValue("@ColourSpec” , PrimaryColours.item(I).Selected) NEXT ‘ Loops until it creates all the related colour spec recordsdcwhisen
Participant
807 Points
202 Posts
Re: Inserting data into SQL Tables from ASP Form
Dec 17, 2009 06:59 PM|LINK
Not asking for much are you? ;) First off take a look at the button1_OnClick method here: http://forums.asp.net/t/1193643.aspx. Secondly to get the scope identity out do something like this:
SqlParameter parmOut = new SqlParameter("@ID", SqlDbType.Int); parmOut.Direction = ParameterDirection.Output; cmd.Parameters.AddWithValue(parmOut); //after you insert run this code with i being the position of the given parameter int id = (int)cmd.Parameters[i].Value;As far as the getdate(), I would set that as the default value for that column in the db itself, therefore you don't have to mess with it.
mattlightbou...
Member
3 Points
53 Posts
Re: Inserting data into SQL Tables from ASP Form
Dec 17, 2009 07:15 PM|LINK
I will get my head around the rest in a little bit - it's 20:13 in the evening and I've cracked open my second beer, it's becoming difficult to concentrate :)
Thanks again.
Matt
dcwhisen
Participant
807 Points
202 Posts
Re: Inserting data into SQL Tables from ASP Form
Dec 17, 2009 07:24 PM|LINK
I don't believe that you can use C# and VB in the same page. I know you can in the same project though. It shouldn't be that hard to change that over to VB, give it a try and if you run into any problems let me know. Crack another open for me!
mattlightbou...
Member
3 Points
53 Posts
Re: Inserting data into SQL Tables from ASP Form
Dec 19, 2009 10:21 AM|LINK
Hi,
Thank you all for your help so far. That link, looks great but, I am trying to get my head around it and convert it to VB, as the rest of my page and page code is in VB.
http://forums.asp.net/t/1193643.aspx
First thing I don't understand is the connection string - the means of connecting to my database. The thing I don't get is, I'm already connected to my database using, from the top of my head, <%connectionstring:connectionstring%>. I developed the SQL database inside Visual Wed Developer with SQL Express running in the background. Whenever I create a new dropdown to the page, it knows to use the connectionstring and then allows me to select a table and enter a query, value and text for the dropdown.
So, I don't think I need all that stuff if it is already established? Unless, there is some difference in my connectionstring if it is to read AND write information?
The first hurdle I want to do is, using a button, create a database record in one table called dbo.Job. I know there is also some scripting to do to get the IDENTITY, and then work out how many additional records in a related table I need to make but, right now, I'm still at the point where I haven't been able to create a single record from my ASPX page.
Let's say for not I have, on the ASPX page.
TextBox - called 'JobName' The TEXT of this to go into JobDescription in the dbo.Job table
DropDown - called 'JobType' The VALUE of this to go into JobTypeID in the dbo.Job table
The only other bit I'd like to know is, if I have a label on my aspx page called JobNumber, how do I do a SELECT IDENTITY of the dbo.Job table and make the label display the Job Number?
Sorry I ask a lot, hopefully, this is simple stuff, I read up so much I end up lost in the process.
Thanks for all your help
Matt
mattlightbou...
Member
3 Points
53 Posts
Re: Inserting data into SQL Tables from ASP Form
Dec 19, 2009 10:24 AM|LINK
Oops, I have got my head around one bit I think
So, lines 1-3 is a FUNCTION and after the button has written the record, I put in line 7.
Is that right?
yrb.yogi
Star
14460 Points
2402 Posts
Re: Inserting data into SQL Tables from ASP Form
Dec 19, 2009 10:27 AM|LINK
changed your code to as
SqlParameter parmOut = new SqlParameter("@ID", SqlDbType.Int); parmOut.Direction = ParameterDirection.Output; cmd.Parameters.Add(parmOut);.Net All About
mattlightbou...
Member
3 Points
53 Posts
Re: Inserting data into SQL Tables from ASP Form
Dec 19, 2009 11:28 AM|LINK
Thanks Yogesh, I appreciate the change to the code. Is that deemed as a Function which STARTS at the top of the button script and ends with the line 7?
Also, any ideas about using the existing connection I have for retrieving information from the database?
Thanks
yrb.yogi
Star
14460 Points
2402 Posts
Re: Inserting data into SQL Tables from ASP Form
Dec 19, 2009 11:41 AM|LINK
use below code to fetch data..
SqlConnection connection = new SqlConnection("server=servername;Initial Catalog=database;User Id=sa;Password=pwd;"); //server=servername;Initial Catalog=database;User Id=sa;Password=pwd; //is your connection string //better to place this in web.config file SqlCommand command = new SqlCommand("Select * from TableName", connection); //If you want to add parameter than use like belw //command.Parameters.Add("@id", SqlDbType.VarChar).Value = textbox.text; SqlDataAdapter adp = new SqlDataAdapter(command); DataTable dt = new DataTable(); adp.Fill(dt);--
and
I am not getting this one....What do you mean by existing connection?
simply use the above code to fetch the data....
thnx
.Net All About
mattlightbou...
Member
3 Points
53 Posts
Re: Inserting data into SQL Tables from ASP Form
Dec 19, 2009 02:41 PM|LINK
Thanks again for your help . What I meant was, I have five dropdowns and for each, I don't need to setup a connection, only a select statement with select parameters
Example:
<asp:DropDownList ID="ClientName" runat="server" AutoPostBack="True" DataSourceID="Clients" DataTextField="ClientSupplierName" DataValueField="ClientSupplierID" Height="23px" Width="170px" AppendDataBoundItems="true"> <asp:ListItem Value="0">Please Select</asp:ListItem> </asp:DropDownList> <asp:SqlDataSource ID="Clients" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [CompanyID], [ClientSupplierID], [ClientSupplierName], [ActiveClientSupplier] FROM [ClientSuppliers] WHERE ([CompanyID] = @CompanyID)"> <SelectParameters> <asp:ControlParameter ControlID="CompanyName" Name="CompanyID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>As far as I am aware, the connection is: