Insert Data Form Xls

Dec 22, 2006

Hello,

I'm trying to make a query to insert data form xls file to my database and it's working

Here's my code

INSERT INTO MY_TABLE
SELECT SAP_NBR, PERIOD, ANSWER, OSAT, SCORE
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:
ipTMP_Aubrygsi_osat.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
WHERE SAP_NBR is not null
AND PERIOD is not null
AND ANSWER is not null
AND OSAT is not null
AND SCORE is not null


And I would like to verify if there no sap_nrb and period already the same in the database
so I add :
AND SAP_NBR not in MY_TABLE.SAP_NBR
AND PERIOD not in MY_TABLE.PERIOD

but it doesn't work

what's the correct syntax to make it work ???

Thanks a lot & merry Xmas... :)

View 8 Replies


ADVERTISEMENT

Insert Post Data From A Form Into Db

Dec 26, 2007

Hi,I'm new at asp .net and have a problem. I have a page where the logged in user can add another user to his list of friends. To do this, I have a page where all the users are listed by using a datalist, and I have a form with an invisible field which passes the value of <%#DataBinder.Eval(Container.DataItem, "UserName")%> in post when the user clicks on "add as a friend" so that in the next page I can get that value and add it as well as the username of the logged in user to the table friendships. This is not working, can anyone help please? The code of the page with the datalist is:<script language="VB" runat="server"> Protected Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs)        ' Session("username") = User.Identity.Name        Dim CurrentUser As String        CurrentUser = Membership.GetUser.ProviderUserKey.ToString()                  End Sub            Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)        Dim DS As DataSet        Dim MyConnection As SqlConnection        Dim MyCommand As SqlDataAdapter        Dim CurrentUser As String        CurrentUser = Membership.GetUser.ProviderUserKey.ToString()                               MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True")        MyCommand = New SqlDataAdapter("select u.*,f.buddyId as userNameIfFriend from aspnet_Users u left outer join aspnet_friendship f ON u.UserId=f.buddyId AND f.userId=@Param1 where IsAnonymous='False' and u.UserId<>@Param1", MyConnection)        'Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|;Integrated Security=True;User Instance=True               MyCommand.SelectCommand.Parameters.AddWithValue("@Param1", CurrentUser)        DS = New DataSet()        MyCommand.Fill(DS, "aspnet_Users")        MyDataList.DataSource = DS.Tables("aspnet_Users").DefaultView        MyDataList.DataBind()                  End Sub</script><body>    <br />  <ASP:DataList id="MyDataList" RepeatColumns="1" runat="server">      <ItemTemplate>        <table cellpadding="10" style="font: 10pt verdana" cellspacing="0">          <tr>            <td width="1" bgcolor="BD8672"/>            <td valign="top">                          </td>            <td valign="top">            <div id="hey"><div id="dados"><b>Name: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br><b>city: </b><%#DataBinder.Eval(Container.DataItem, "City")%><br></div>               <div id="photo"><b>Photo: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br>status: <form id="teste" name="teste" method="post" action="add_buddy.aspx">                                   <input name="UserId" type="hidden" id="UserId" value="<%#DataBinder.Eval(Container.DataItem, "UserName")%>" />                </form>                                <%#IIf(Container.DataItem("userNameIfFriend") Is DBNull.Value, "<a href='add_buddy.aspx'>Add as buddy</a> ", "Already buddy")%>         </div>                            <p></div>              <a href='<%# DataBinder.Eval(Container.DataItem, "UserName", "purchase.aspx?titleid={0}") %>' >                                                         </a>            &nbsp;&nbsp;</td>          </tr>        </table>      </ItemTemplate>  </ASP:DataList></body></asp:Content>The code of the page where the user is redirected to add a friend and where I want to make the insert is:  <script runat="server">    Protected Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs)        Dim CurrentUser As String        CurrentUser = Membership.GetUser.ProviderUserKey.ToString()                  End Sub        Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)        Dim CurrentUser As String        CurrentUser = Membership.GetUser.ProviderUserKey.ToString()                ' Declaring variables        Dim UserId As String        ' A Function to check if some field entered by user is empty                ' Receiving values from Form        UserId = (Request.Form("UserId"))               Dim MyConnection As SqlConnection        Dim MyCommand As SqlDataAdapter                   MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True")                MyCommand = New SqlDataAdapter("insert @Param2, @Param1 into aspnet_friendship.userId, aspnet_friendship.buddyId", MyConnection)        MyCommand.SelectCommand.Parameters.AddWithValue("@Param1", CurrentUser)        MyCommand.SelectCommand.Parameters.AddWithValue("@Param2", UserId)             ' Creating Connection Object and opening the database        Dim DS As DataSet        DS = New DataSet()        MyCommand.Fill(DS, "aspnet_friendship")                ' Done. Close the connection                End Sub    </script> I am not sure if sending the information in a form in post to another page and executing the query there is the best option or if I can do it all within
the if statement. I appreciate any help, since I am having difficulties in solving this.Thank you.   

View 3 Replies View Related

I'm Not Able To Insert Data From A Form Into A Database Table

Feb 18, 2007

Hello.  As the subject heading says, I'm not able to insert data typed into the contact form on my page into a database table.  I'm using an SqlDataSource object.  Here's the code for this page:
 
<?xml version="1.0" encoding="iso-8859-1"?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><%@ Page Language="VB" Debug="True" Explicit="True" ContentType="text/html" ResponseEncoding="iso-8859-1" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.Odbc" %><html xmlns="http://www.w3.org/1999/xhtml"><head><title>Untitled Document</title><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /></head><body><div align="center">  <asp:label ID="label1" runat="server" Font-Name="Arial" Font-Size="24" ForeColor="#00FF00" /></div><div align="center">  <asp:label ID="label2" runat="server" Font-Name="Verdana" Font-Size="18" ForeColor="#0000FF" /></div><form runat="server" id="new_form">  <table width="50%" border="1" align="center" id="form_table">    <tr>       <td nowrap="nowrap">         <asp:label Font-Bold="true" Font-Size="10" runat="server" Font-Name="CarlysHand" Text="First Name" BackColor="#FFE1E1" ForeColor="#FF0000" /></td>      <td><asp:textbox text='<%#Bind("FirstName") %>' Font-Size="10" BorderColor="#BF0000" BorderStyle="Groove" BorderWidth="2" BackColor="#FFCCFF" ForeColor="#FF0000" Columns="15" TextMode="SingleLine" Font-Bold="true" Font-Name="CarlysHand" runat="server" ID="first" TabIndex="1" /></td>      <asp:requiredfieldvalidator runat="server" ControlToValidate="first" Type="String" ErrorMessage="Please enter your first name." Display="Dynamic" /> </tr>    <tr>       <td nowrap="nowrap">         <asp:label Font-Size="10" Font-Bold="true" runat="server" Font-Name="CarlysHand" Text="Middle Initial" BackColor="#FFE1E1" ForeColor="#FF0000" /></td>      <td><asp:textbox text='<%#Bind("MI") %>' Font-Size="10" BorderColor="#BF0000" BorderStyle="Groove" BorderWidth="2" BackColor="#FFCCFF" ForeColor="#FF0000" Columns="1" TextMode="SingleLine" Font-Bold="true" Font-Name="CarlysHand" runat="server" ID="mi" TabIndex="2" /></td>    </tr>    <tr>       <td nowrap="nowrap">         <asp:label Font-Size="10" Font-Bold="true" runat="server" Font-Name="CarlysHand" Text="Last Name" BackColor="#FFE1E1" ForeColor="#FF0000" /></td>      <td><asp:textbox text='<%#Bind("LastName") %>' Font-Size="10" BorderColor="#BF0000" BorderStyle="Groove" BorderWidth="2" BackColor="#FFCCFF" ForeColor="#FF0000" Columns="18" TextMode="SingleLine" Font-Bold="true" Font-Name="CarlysHand" runat="server" ID="last" TabIndex="3" /></td>      <asp:requiredfieldvalidator runat="server" ControlToValidate="last" Type="String" ErrorMessage="Please enter your last name." Display="Dynamic" />    </tr>    <tr>       <td nowrap="nowrap">         <asp:label Font-Size="10" Font-Bold="true" runat="server" Font-Name="CarlysHand" Text="Number & Street" BackColor="#FFE1E1" ForeColor="#FF0000" /></td>      <td><asp:textbox text='<%#Bind("NoAndStreet") %>' Font-Size="10" BorderColor="#BF0000" BorderStyle="Groove" BorderWidth="2" BackColor="#FFCCFF" ForeColor="#FF0000" Columns="40" TextMode="SingleLine" Font-Bold="true" Font-Name="CarlysHand" runat="server" ID="no_and_street" TabIndex="4" /></td>      <asp:requiredfieldvalidator runat="server" ControlToValidate="no_and_street" Type="String" ErrorMessage="Please enter your number and street." Display="Dynamic" />    </tr>    <tr>       <td nowrap="nowrap">         <asp:label Font-Size="10" Font-Bold="true" runat="server" Font-Name="CarlysHand" Text="Unit #" BackColor="#FFE1E1" ForeColor="#FF0000" />      </td>      <td><asp:textbox text='<%#Bind("Unit") %>' Font-Size="10" BorderColor="#BF0000" BorderStyle="Groove" BorderWidth="2" BackColor="#FFCCFF" ForeColor="#FF0000" Columns="6" TextMode="SingleLine" Font-Bold="true" Font-Name="CarlysHand" runat="server" ID="unit" TabIndex="5" /></td>    </tr>    <tr>       <td nowrap="nowrap">         <asp:label Font-Size="10" Font-Bold="true" runat="server" Font-Name="CarlysHand" Text="City" BackColor="#FFE1E1" ForeColor="#FF0000" />      </td>      <td><asp:textbox text='<%#Bind("City") %>' Font-Size="10" BorderColor="#BF0000" BorderStyle="Groove" BorderWidth="2" BackColor="#FFCCFF" ForeColor="#FF0000" Columns="20" TextMode="SingleLine" Font-Bold="true" Font-Name="CarlysHand" runat="server" ID="city" TabIndex="6" /></td>      <asp:requiredfieldvalidator runat="server" ControlToValidate="city" Type="String" ErrorMessage="Please enter your city." Display="Dynamic" />    </tr>    <tr>       <td nowrap="nowrap">         <asp:label Font-Size="10" Font-Bold="true" runat="server" Font-Name="CarlysHand" Text="State" BackColor="#FFE1E1" ForeColor="#FF0000" /></td>      <td><asp:textbox text='<%#Bind("State") %>' Font-Size="10" BorderColor="#BF0000" BorderStyle="Groove" BorderWidth="2" BackColor="#FFCCFF" ForeColor="#FF0000" Columns="2" TextMode="SingleLine" Font-Bold="true" Font-Name="CarlysHand" runat="server" ID="state" TabIndex="7" /></td>      <asp:requiredfieldvalidator runat="server" ControlToValidate="state" ErrorMessage="Please enter your state." Type="String" Display="Dynamic" />    </tr>    <tr>       <td nowrap="nowrap">         <asp:label Font-Size="10" Font-Bold="true" runat="server" Font-Name="CarlysHand" Text="Zip Code" BackColor="#FFE1E1" ForeColor="#FF0000" /></td>      <td><asp:textbox text='<%#Bind("ZipCode") %>' Font-Size="10" BorderColor="#BF0000" BorderStyle="Groove" BorderWidth="2" BackColor="#FFCCFF" ForeColor="#FF0000" Columns="5" TextMode="SingleLine" Font-Bold="true" Font-Name="CarlysHand" runat="server" ID="zip" TabIndex="8" /></td>      <asp:requiredfieldvalidator runat="server" ControlToValidate="zip" ErrorMessage="Please enter your zip code." Type="Integer" Display="Dynamic" />   <asp:regularexpressionvalidator Display="Dynamic" runat="server" ValidationExpression="[0123456789]{5}" ControlToValidate="zip" ErrorMessage="Please enter a valid US Zip Code" />    </tr>    <tr>       <td nowrap="nowrap">         <asp:label Font-Size="10" Font-Bold="true" runat="server" Font-Name="CarlysHand" Text="Phone #" BackColor="#FFE1E1" ForeColor="#FF0000" /></td>      <td><asp:textbox text='<%#Bind("PhoneNumber") %>' Font-Size="10" BorderColor="#BF0000" BorderStyle="Groove" BorderWidth="2" BackColor="#FFCCFF" ForeColor="#FF0000" Columns="10" TextMode="SingleLine" Font-Bold="true" Font-Name="CarlysHand" runat="server" ID="phone" TabIndex="9" /></td>      <asp:requiredfieldvalidator runat="server" ControlToValidate="phone" ErrorMessage="Please enter your phone number." Type="Double" Display="Dynamic" />      <asp:rangevalidator runat="server" ControlToValidate="phone" MinimumValue="2002000000" MaximumValue="9999999999" Type="Double" ErrorMessage="Please enter a valid US Phone Number." Display="Dynamic" />    </tr>    <tr>       <td nowrap="nowrap">         <asp:label Font-Size="10" Font-Bold="true" runat="server" Font-Name="CarlysHand" Text="Email" BackColor="#FFE1E1" ForeColor="#FF0000" /></td>      <td><asp:textbox text='<%#Bind("Email") %>' Font-Size="10" BorderColor="#BF0000" BorderStyle="Groove" BorderWidth="2" BackColor="#FFCCFF" ForeColor="#FF0000" Columns="40" TextMode="SingleLine" Font-Bold="true" Font-Name="CarlysHand" runat="server" ID="email" TabIndex="10" /></td>      <asp:requiredfieldvalidator runat="server" ControlToValidate="email" ErrorMessage="Please enter your email address." Type="String" Display="Dynamic" />      <asp:regularexpressionvalidator runat="server" ControlToValidate="email" ErrorMessage="Please enter a valid email address." ValidationExpression=".*@.{2,}..{2,}" Display="Dynamic" />    </tr>    <tr>       <td nowrap="nowrap">         <asp:label Font-Bold="true" runat="server" Font-Name="CarlysHand" Text="Username" BackColor="#FFE1E1" ForeColor="#FF0000" /></td>      <td><asp:textbox text='<%#Bind("Username") %>' BorderColor="#BF0000" BorderStyle="Groove" BorderWidth="2" BackColor="#FFCCFF" ForeColor="#FF0000" Columns="12" TextMode="SingleLine" Font-Bold="true" Font-Name="CarlysHand" runat="server" ID="username" TabIndex="11" /></td>      <asp:requiredfieldvalidator runat="server" ControlToValidate="username" ErrorMessage="Please enter your username." Type="String" Display="Dynamic" />    </tr>    <tr>       <td nowrap="nowrap">         <asp:label Font-Size="10" Font-Bold="true" runat="server" Font-Name="CarlysHand" Text="Password" BackColor="#FFE1E1" ForeColor="#FF0000" /></td>      <td> <asp:textbox text='<%#Bind("Password") %>' Font-Size="10" BorderColor="#BF0000" BorderStyle="Groove" BorderWidth="2" BackColor="#FFCCFF" ForeColor="#FF0000" Columns="12" TextMode="Password" Font-Bold="true" Font-Name="CarlysHand" runat="server" ID="password" TabIndex="12" /></td>      <asp:requiredfieldvalidator runat="server" ControlToValidate="password" ErrorMessage="Please enter your password." Type="String" Display="Dynamic" />    </tr>    <tr>       <td nowrap="nowrap">         <asp:label Font-Size="10" Font-Bold="true" runat="server" Font-Name="CarlysHand" Text="Confirm Password" BackColor="#FFE1E1" ForeColor="#FF0000" /></td>      <td><asp:textbox Font-Size="10" BorderColor="#BF0000" BorderStyle="Groove" BorderWidth="2" BackColor="#FFCCFF" ForeColor="#FF0000" Columns="12" TextMode="Password" Font-Bold="true" Font-Name="CarlysHand" runat="server" ID="confirm" TabIndex="13" /></td>      <asp:requiredfieldvalidator runat="server" ControlToValidate="confirm" ErrorMessage="Please confirm your password." Type="String" Display="Dynamic" />      <asp:comparevalidator runat="server" ControlToValidate="confirm" ControlToCompare="password" Type="String" ErrorMessage="Please input matching passwords." Display="Dynamic" />    </tr>    <tr>       <td colspan="2" align="center" nowrap="nowrap"><asp:linkbutton BorderColor="#0000FF" BorderWidth="3" BorderStyle="Dotted" ToolTip="Click me!" Text="Submit" Font-Name="CarlysHand" Font-Size="15" Font-Bold="true" BackColor="#CCCCCC" ForeColor="#FF0000" runat="server" ID="submit" TabIndex="14" /></td>    </tr>  </table>   <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="driver={MySQL ODBC 3.51 Driver};server=mysql01.discountasp.net; data source=bkfwebdesi1_mysqlConn;database=MYSQLDB_316823;uid=bkfwebdesi1; password=bkf2065" ProviderName="System.Data.Odbc"   InsertCommand="INSERT INTO ContactInfo VALUES (?, ?, ?, ?, ?, ?, ?, ?,       ?, ?, ?, ?, ?)">     <InsertParameters>  <asp:Parameter Name="ContactID" Type="Int32" />  <asp:Parameter Name="FirstName" Type="String" />  <asp:Parameter Name="MI" Type="String" />  <asp:Parameter Name="LastName" Type="String" />  <asp:Parameter Name="NoAndStreet" Type="String" />  <asp:Parameter Name="Unit" Type="String" />  <asp:Parameter Name="City" Type="String" />  <asp:Parameter Name="State" Type="String" />  <asp:Parameter Name="ZipCode" Type="Int32" />  <asp:Parameter Name="PhoneNumber" Type="String" />  <asp:Parameter Name="Email" Type="String" />  <asp:Parameter Name="Username" Type="String" />  <asp:Parameter Name="Password" Type="String" /> </InsertParameters>  </asp:SqlDataSource></form></body></html>
 
Thanks in advance for your help in this matter!
 
Brian

View 1 Replies View Related

Trying To Insert Form Controls And Another Data Source

Mar 24, 2007

I'm trying to insert data into a table from two sources (a table and form controls) at the same time.   
--this data from the tradeitem table and is inserted into the selections table
--based on the variable @builderid --no problem it works fine as is
INSERT INTO Selections ([TradeItemID], [TradeID], [ProductName], [OrigSalesPrice], [RevSalesPrice])
SELECT [TradeItemID], [TradeID], [TradeItem], [Price], [Price]
FROM tradeitems
WHERE BuilderID = '1'
However, I need these also to pull from the form controls
--These variables will pull from form controls and need to be associated
--with each record added to the seleciton table
INSERT INTO Selections ([UserDataID] = '1', [DefaultQty] = '0')
--How do I get these combined into one statement so I get the following result?












TradeItemID
TradeID
ProductName
OrigSalesPrice
RevSalesPrice
UserDataID
DefaultQty

1
72
HVAC
50
50
1
0

2
36
Plumbing
100
100
1
0

3
99
Electrical
100
100
1
0

4
4
Pain
25
25
1
0
Thanks in advance

View 2 Replies View Related

Insert Command In SQL Form

Jan 12, 2006

Good morning,
I don't know where is the problem here, but I can not add a new record in the SQL database related. Can you take a look at the code please and let me know what's wrong? When I press the button to insert the data is automatically reloads without any input in the database.
Thanks in advance,Alejandro.
<%@ Page Language="VB" Debug="true" %>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>Untitled Page</title>
</head>
<body>
<form id="form1" method="post" runat="server">
<asp:SqlDataSource id="ds1" runat="server"
ConnectionString="server=62.149.153.13;database=MSSql13067;uid=MSSql13067;pwd=d7f15bd2"
SelectCommand="SELECT [usuario], [emailes] FROM [usuarioyemail]"
InsertCommand="INSERT INTO [usuarioyemail] ([usuario], [emailes]) VALUES (@usuariocontrol, @emailcontrol)"
>
<InsertParameters>
<asp:ControlParameter Name="usuariocontrol" ControlID="usuario" Type="Char" PropertyName="Text" />
<asp:ControlParameter Name="emailcontrol" ControlID="email" Type="Char" PropertyName="Text" />
</InsertParameters>
</asp:SqlDataSource>
<asp:TextBox ID="usuario" runat="server"></asp:TextBox>
<asp:TextBox ID="email" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Button" />
</form>
</body>
</html>

View 3 Replies View Related

Simple Form/DB Insert Question

Aug 1, 2006

I'm trying to wrap my head around inserting values into a table w/ SQLDataSource. What I'm trying to do is have a form where users can submit their feedback and their responses would get put into a table on the backend. It would store not only their comments, but their userid, the url that they were on last, and which area they're from. The trouble is, I have no idea if I'm coding this correctly. On the submit Here is the form:    <form id="form1" runat="server">    <asp:SqlDataSource runat="server" ID="feedbackSQL" InsertCommand="insertfeedback_sp" InsertCommandType="StoredProcedure" connectionstring="<%$ ConnectionStrings:Test%>">        <InsertParameters>            <asp:Parameter Name="ID" Type="Int16" />            <asp:Parameter Name="Region" Type="String" />            <asp:Parameter Name="Querystring" Type="String" />            <asp:FormParameter Name="Comments" FormField="Comment" />        </InsertParameters>        </asp:SqlDataSource>    <div>        <asp:TextBox ID="Comment" runat="server" Height="200px" Width="350px"></asp:TextBox><br />        <asp:Button ID="Submit" OnClientClick="btn_Submit" runat="server" BackColor="White"  Font-Names="Arial" Font-Size="X-Small"            Text="Submit" />&nbsp;&nbsp;        <asp:Button ID="Cancel" runat="server" BackColor="White"  Font-Names="Arial" Font-Size="X-Small"            Text="Cancel" OnClientClick='javascript: window.close()' />&nbsp; &nbsp;        <asp:Button ID="Clear" runat="server" BackColor="White"  Font-Names="Arial" Font-Size="X-Small"            Text="Clear" OnClientClick='javascript: ClearBox(Comment)' />    </div>    </form>Here is the backend:    protected void btn_Submit(object sender, EventArgs e)    {        feedbackSQL.InsertParameters["ID"].DefaultValue = UserId ;        feedbackSQL.InsertParameters["Region"].DefaultValue = Region;        feedbackSQL.InsertParameters["Comments"].DefaultValue = Comment.Text.ToString();        feedbackSQL.InsertParameters["Querystring"].DefaultValue = URL.ToString();    }When I test it out. Nothing shows up in my database table. I also have another problem, ID is stored in my table as an int, but when I try to set it's value in btn_Submit and try to compile it, I get a "cannont convert 'int' to 'string'" error. Am I missing something here? Any help would be much appreciated, thanks :)

View 6 Replies View Related

Select Form 1 Table And Insert Into Another

Jan 30, 2004

Can someone point me in the right direction or show me a sample that may assist me in the following.

Thanks,
-dw


I need to select some data from 1 table and insert it into another using a stored procedure.

I also would like to select data from table 1 and insert a new record into table 1 modifying a field with new data passed by parameter.

Is it something like:

parameters passed = @selectJobNumber, @newJobNumber


declare @DeliveryMethodIDint

SELECT
@DeliveryMethodID=DeliveryMethodID,
... etc - more fields

FROM
jobDeliveryAddress

WHERE
(JobNumber= @selectJobNumber)


INSERT INTO [jobDeliveryAddress]
(
[JobNumber],
[DeliveryMethodID],
... etc - more fields

)
VALUES
(
@newJobNumber,
@DeliveryMethodID,
... etc

)

View 2 Replies View Related

Select Form 1 Table And Insert Into Another

Jan 30, 2004

Can someone point me in the right direction or show me a sample that may assist me in the following.

Thanks,
-dw


I need to select some data from 1 table and insert it into another using a stored procedure.

I also would like to select data from table 1 and insert a new record into table 1 modifying a field with new data passed by parameter.

Is it something like:

parameters passed = @selectJobNumber, @newJobNumber


declare @DeliveryMethodIDint

SELECT
@DeliveryMethodID=DeliveryMethodID,
... etc - more fields

FROM
jobDeliveryAddress

WHERE
(JobNumber= @selectJobNumber)


INSERT INTO [jobDeliveryAddress]
(
[JobNumber],
[DeliveryMethodID],
... etc - more fields

)
VALUES
(
@newJobNumber,
@DeliveryMethodID,
... etc

)

View 6 Replies View Related

Trying To Insert Form Info Into Database Using Sqldatasource And Wizard

Jan 7, 2008

Creating custom Register form That inserts user info in to database via storeprocgetting following errorCompilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30456: 'ValidateInput' is not a member of 'System.Web.UI.WebControls.TextBox'.

Source Error:


Line 3496: Me.__BuildControlTree(Me)
Line 3497: Me.AddWrappedFileDependencies(Global.ASP.partfrm_aspx.__fileDependencies)
Line 3498: Me.Request.ValidateInput
Line 3499: End Sub
Line 3500:

>.ASPX <asp:Wizard ID="AddParticipantWizard" OnFinishButtonClick="AddParticipant_FinishButtonClick" FinishDestinationPageUrl="~/partReg.aspx" runat="server">
<WizardSteps>
<asp:WizardStep ID="WizardStep1" runat="server" Title="Register Participant">
<h3>&nbsp; &nbsp;Register: &nbsp;</h3>
<hr />
<table border="0">

<tr>
<td align="Left" colspan="2">
<h3>
&nbsp; &nbsp;PARTICIPANT INFORMATION: &nbsp;</h3><hr />
Please enter information for the attendee and click 'continue'.</td>

</tr></table>


<table border="0">

<tr>
<td align="right"> Registrant Information&nbsp;&nbsp;
</td>
<td style="width: 232px">
<asp:CheckBox ID="CheckBox1" runat="server" /> Register Me</td>
</tr>

<tr>
<td align="Left" colspan="2">
<table border="0">


<tr>
<td align="right">
<asp:Label ID="FirstNameLabel" runat="server" AssociatedControlID="firstname">First Name:</asp:Label>
</td>
<td style="width: 232px">
<asp:TextBox ID="firstname" runat="server" Width="195px"></asp:TextBox>
<asp:RequiredFieldValidator ID="FirstNameRequired" runat="server" ControlToValidate="firstname"
ErrorMessage="First Name is required." ToolTip="First Name is required." ValidationGroup="AddParticipantWizard">*</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td align="right">
<asp:Label ID="LastNameLabel" runat="server" AssociatedControlID="lastname">Last Name:</asp:Label>
</td>
<td style="width: 232px">
<asp:TextBox ID="lastname" runat="server" Width="195px"></asp:TextBox>
<asp:RequiredFieldValidator ID="LastNameRequired" runat="server" ControlToValidate="lastname"
ErrorMessage="Last Name is required." ToolTip="Last Name is required." ValidationGroup="AddParticipantWizard">*</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td align="right">
<asp:Label ID="EmailLabel" runat="server" AssociatedControlID="Email">E-mail:</asp:Label>
</td>
<td style="width: 232px">
<asp:TextBox ID="Email" runat="server" Width="195px"></asp:TextBox>
<asp:RequiredFieldValidator ID="EmailRequired" runat="server" ControlToValidate="Email"
ErrorMessage="E-mail is required." ToolTip="E-mail is required." ValidationGroup="AddParticipantWizard">*</asp:RequiredFieldValidator>
</td>
</tr>
<tr><td style="width: 232px"><br /></td></tr>

<tr>
<td align="right" style="height: 32px">
<asp:Label ID="StreetLabel" runat="server" AssociatedControlID="Street">Street:</asp:Label>
</td>
<td style="width: 232px; height: 32px">
<asp:TextBox ID="Street" runat="server" Width="195px"></asp:TextBox>
<asp:RequiredFieldValidator ID="StreetRequired" runat="server" ControlToValidate="Street"
ErrorMessage="Street is required." ToolTip="Street is required." ValidationGroup="AddParticipantWizard">*</asp:RequiredFieldValidator>

</td>
</tr>
<tr><td></td>
<td> <br />
<br /> ".
</td></tr>

<tr>
<td align="right">
<asp:Label ID="CityLabel" runat="server" AssociatedControlID="City">City:</asp:Label>

</td>
<td style="width: 232px">
<asp:TextBox ID="City" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="CityRequired" runat="server" ControlToValidate="City"
ErrorMessage="City is required." ToolTip="City is required." ValidationGroup="AddParticipantWizard">*</asp:RequiredFieldValidator>

</td>
</tr>
<tr>
<td align="right">

<asp:Label ID="StateLabel" runat="server" AssociatedControlID="State">State:</asp:Label>

</td>
<td style="width: 232px">

<asp:TextBox ID="State" runat="server" Width="18px">

&nbsp;</asp:TextBox><asp:RequiredFieldValidator ID="StateRequired" runat="server" ControlToValidate="State"
ErrorMessage="State is required." ToolTip="State is required." ValidationGroup="AddParticipantWizard">*</asp:RequiredFieldValidator>

</td>
</tr>
<tr><td></td>
<td> <br /> <br /> </td></tr>
<tr><td style="width: 232px"><br /></td></tr>
<tr>
<td align="right">

<asp:Label ID="PostalCodeLabel" runat="server" AssociatedControlID="Zip">Postal/Zip<br />
Code:</asp:Label>
</td>
<td style="width: 232px">

<asp:TextBox ID="PostalCode" runat="server" Width="28px"></asp:TextBox>
<asp:RequiredFieldValidator ID="PostalCodeRequired" runat="server" ControlToValidate="PostalCode"
ErrorMessage="PostalCode is required." ToolTip="PostalCode is required." ValidationGroup="AddParticipantWizard">*</asp:RequiredFieldValidator>

</td>
</tr>
<tr>
<td align="right">
<asp:Label ID="OrganizationLabel" runat="server" AssociatedControlID="Organization">Organization:</asp:Label>
</td>
<td style="width: 232px">
<asp:TextBox ID="Organization" runat="server" Width="195px"></asp:TextBox>
<asp:RequiredFieldValidator ID="OrganizationRequired" runat="server" ControlToValidate="Organization"
ErrorMessage="Organization is required." ToolTip="Organization is required." ValidationGroup="AddParticipantWizard">*</asp:RequiredFieldValidator>

</td>
</tr>
<tr>
<td align="right">
<asp:Label ID="TitleLabel" runat="server" AssociatedControlID="Title">Title:</asp:Label>
</td>
<td style="width: 232px">
<asp:TextBox ID="Title" runat="server" Width="195px"></asp:TextBox>
<asp:RequiredFieldValidator ID="TitleRequired" runat="server" ControlToValidate="Title"
ErrorMessage="Title is required." ToolTip="Title is required." ValidationGroup="AddParticipantWizard">*</asp:RequiredFieldValidator>

</td>
</tr>
<tr>
<td align="right">
<asp:Label ID="DepartmentLabel" runat="server" AssociatedControlID="Department">Department:</asp:Label>
</td>
<td style="width: 232px">
<asp:TextBox ID="Department" runat="server" Width="195px"></asp:TextBox>
<asp:RequiredFieldValidator ID="DepartmentRequired" runat="server" ControlToValidate="Department"
ErrorMessage="Department is required." ToolTip="Department is required." ValidationGroup="AddParticipantWizard">*</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td align="right">
<asp:Label ID="PhoneNumberLabel" runat="server" AssociatedControlID="Phone">Phone Number:</asp:Label>
</td>
<td style="width: 232px">
<asp:TextBox ID="Phone" runat="server" Width="195px"></asp:TextBox>
<asp:RequiredFieldValidator ID="PhoneRequired" runat="server" ControlToValidate="Phone"
ErrorMessage="Phone Number is required." ToolTip="Phone Number is required."
ValidationGroup="AddParticipantWizard">*</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td align="right">
<asp:Label ID="RequestLabel" runat="server" AssociatedControlID="Request">Any special request:</asp:Label>
</td>
<td style="width: 232px">(e.g, )

</td>
</tr>
<tr>
<td align="right">
</td>
<td style="width: 232px">
<asp:TextBox ID="Request" runat="server" Width="235px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequestRequiredFieldValidator1" runat="server" ControlToValidate="Request"
ErrorMessage="Request is required." ToolTip="Request is required."
ValidationGroup="AddParticipantWizard">*</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td align="center" colspan="2">
<asp:RegularExpressionValidator ID="EmailRegExp" runat="server" ControlToValidate="Email"
Display="Dynamic" ErrorMessage="The email format is invalid." ValidationExpression="S+@S+.S+"
ValidationGroup="AddParticipantWizard"></asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td align="center" colspan="2" style="color: red">
<asp:Literal ID="ErrorMessage" runat="server" EnableViewState="False"></asp:Literal>
</td>
</tr>
<tr><td></td>
<td align="right">

</td></tr>

</table>
</td>
</tr>
</table>


<asp:SqlDataSource ID="InsertPartDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" InsertCommand="PartInsert" InsertCommandType="StoredProcedure">
<InsertParameters>
<asp:ControlParameter ControlID="firstName" Name="firstName" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="lastName" Name="lastName" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="Email" Name="Email" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="Street" Name="Street" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="City" Name="City" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="State" Name="State" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="PostalCode" Name="PostalCode" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="Organization" Name="Organization" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="Title" Name="Title" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="Department" Name="Department" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="Phone" Name="Phone" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="Request" Name="Request" PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="Assistance" Name="Assistance" PropertyName="SelectedValue"
Type="String" />
</InsertParameters>
</asp:SqlDataSource>
</asp:WizardStep>
<asp:WizardStep ID="WizardStep2" runat="server" Title="Completion">

<p>
Your account has been successfully created.
</p>
<asp:Button ID="ContinueButton" runat="server" CommandName="Continue"
Text="Continue" />


</asp:WizardStep>
</WizardSteps>
</asp:Wizard>  > CODE-BEHIND 'This event handler fires when the user clicks Finish. We need to insert the new participant record into the database
Protected Sub AddParticipant_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles AddParticipantWizard.FinishButtonClick
'Insert the Participant into the database
InsertPartDataSource.Insert()

'Send the user back to the homepage
Response.Redirect("~/PartReg.aspx")
End Sub

View 1 Replies View Related

Insert Query Fails (if Form Fields Left Empty)

Aug 13, 2007

Dear All,
I have created a table in my SQL server database, the problem i am facing is my insert query fails if i leave any form field empty (leave it blank). On my back-end table, only one field is mandatory, and others have been set with the constraint "allow null".
As per our business requirement, except one value is complusory while others are optional. If I enter all values in the form it works perfectly fine. Can you see in the code below - where am i possibly going wrong ?
<script language="VB" runat="server" >      Sub Page_Load(Src As Object, e As EventArgs)                      If Page.IsPostBack Then                        Dim ConLath As SqlConnection            Dim comLath As SqlCommand            Dim insertcmd                        conLath = New SqlConnection("Data Source=SQLas;Initial Catalog=settle;User ID=sa;Password=password")            ConLath.Open()            insertcmd = "Insert into His_set values (@t_d,@s_p,@p_s,@v_oq,@i_oq,@v_qn,@i_qn,@v_qw,@i_qw)"                        comLath = New SqlCommand(insertcmd, ConLath)                                    comLath.Parameters.Add(New SqlParameter("@t_d", SqlDbType.DateTime, 12))            comLath.Parameters("@t_d").Value = trade_date.Text            comLath.Parameters.Add(New SqlParameter("@s_p", SqlDbType.Decimal, 8))            comLath.Parameters("@s_p").Value = sett_price.Text            comLath.Parameters.Add(New SqlParameter("@p_s", SqlDbType.Decimal, 8))            comLath.Parameters("@p_s").Value = post_close.Text            comLath.Parameters.Add(New SqlParameter("@v_oq", SqlDbType.Int, 8))            comLath.Parameters("@v_oq").Value = vol_oq.Text            comLath.Parameters.Add(New SqlParameter("@i_oq", SqlDbType.Int, 8))            comLath.Parameters("@i_oq").Value = oi_oq.Text            comLath.Parameters.Add(New SqlParameter("@v_qn", SqlDbType.Int, 8))            comLath.Parameters("@v_qn").Value = vol_qn.Text            comLath.Parameters.Add(New SqlParameter("@v_qw", SqlDbType.Int, 8))            comLath.Parameters("@v_qw").Value = vol_qw.Text            comLath.Parameters.Add(New SqlParameter("@i_qn", SqlDbType.Int, 8))            comLath.Parameters("@i_qn").Value = oi_qn.Text            comLath.Parameters.Add(New SqlParameter("@i_qw", SqlDbType.Int, 8))            comLath.Parameters("@i_qw").Value = oi_qw.Text
                        Try                comLath.ExecuteNonQuery()                            Catch ex As SqlException                If ex.Number = 2627 Then                    Message.InnerHtml = "ERROR: A record already exists with " _                       & "the same primary key"                Else                    Message.InnerHtml = "ERROR: Could not add record, please " _                       & "ensure the fields are correctly filled out"                    Message.Style("color") = "red"                End If            End Try
            comLath.Dispose()            ConLath.Close()                                                        End If   End Sub
</script>
 

View 6 Replies View Related

One Form To Update Different SQL Tables Based Upon Form Selection

Apr 16, 2008

Hello,
My company Intranet has a form that agents can use to post their comments about the company to upper management, but our customer service department would like to modify the form so that the agent has to pick from a comment type.
The dropdown options on the form will be as follows:
ComplimentsComplaintsGeneral CommentsSuggestions
Each dropdown option has a designated table in a SQL DB.Using postback on the same page, I need to change which fields of the form are visible based upon which dropdown selection the user chooses, and I need the fields to then be inserted into the table that corresponds with the dropdown selection item.
For example: If the Compliments dropdown selection is picked, I need a text box to show for the user's location, the name of the customer, account number, and the message box. Once the submit button is clicked, the characters in these boxes need to be inserted into the Compliments table using its data adapter.
However, if the user selects Suggestions, the name of the customer and the account number should not be visible, since these fields do not exist and when the submit button is pressed, the Suggestions table should be updated.
If you need more information, I will provide whatever is needed.
As always, thanks for everyone's assistance.
Chris

View 3 Replies View Related

How To Add Data To SQL Via Web Form?

Feb 19, 2008

HiI am using Visual Web Developer 2005 Express and SQL Server Express 2005.From Northwind I can display data to Gridview.Could someone point me in the right direction on two points.How do I start a new database (.mdf) - I cannot find this option.I can change Northwind around - but I want to start my own database.
Once I have that sorted - how can I "insert" data into database using a dropdown box?A "dropdown" box such as you would find online.
Thanks in advance.Stephen

View 2 Replies View Related

INSERTING DATA WITH WEB FORM

Apr 25, 2008

I need help to resolve the following problem. 
I have a form that is supposed to insert data into my data base. But I get an error message when I click submit.
Below is the Code and after that is the error message that I get. Thank you in advance.
 
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
End SubProtected Sub insertSubmitBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs)
SqlDataSource1.Insert()End Sub
</script><html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
<script type="text/javascript">
 function pageLoad() {
}
 
</script>
<style type="text/css">
.style1
{width: 34%;
}
.style2
{width: 144px;
}</style>
</head><body>
<br />
 
<form id="form1" runat="server">
 
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server" />
 
<br /><asp:SqlDataSource ID="SqlDataSource1" runat="server"
InsertCommand="INSERT INTO property(provinceName, cityName, ownerName, ownerPhone, numOfRooms, rent, ownerEmail, listPeriod, addInfo)
VALUES(@provinceName, @cityName, @ownerName, @ownerPhone, @numOfRooms, @rent, @ownerEmail, @listPeriod, @addInfo)">
<InsertParameters><asp:ControlParameter ControlID="provinceddl2" Name="provinceName"
PropertyName="SelectedValue" /><asp:ControlParameter ControlID="cityddl2" Name="cityName"
PropertyName="SelectedValue" /><asp:ControlParameter ControlID="nameTxtBox" Name="ownerName"
PropertyName="Text" /><asp:ControlParameter ControlID="phoneTxtBox" Name="ownerPhone"
PropertyName="Text" /><asp:ControlParameter ControlID="roomsTxtBox" Name="numOfRooms"
PropertyName="Text" />
<asp:ControlParameter ControlID="rentTxtBox" Name="rent" PropertyName="Text" /><asp:ControlParameter ControlID="EmailTxtBox" Name="ownerEmail"
PropertyName="Text" /><asp:ControlParameter ControlID="listPeriodddl" Name="listPeriod"
PropertyName="SelectedValue" /><asp:ControlParameter ControlID="addInfoTxtBox" Name="addInfo"
PropertyName="Text" />
</InsertParameters>
</asp:SqlDataSource>
<br />
<br />
<br />
<br />
 
<table cellspacing="1" class="style1">
<tr>
<td class="style2">
Contact Name:</td>
<td>
<asp:TextBox ID="nameTxtBox" runat="server" Width="200px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Contact phone:</td>
<td>
<asp:TextBox ID="phoneTxtBox" runat="server" Width="200px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Province:</td>
<td>
<asp:DropDownList ID="provinceddl2" runat="server" Width="205px">
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="style2">
City:</td>
<td>
<asp:DropDownList ID="cityddl2" runat="server" Width="205px">
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="style2">
Email:</td>
<td>
<asp:TextBox ID="EmailTxtBox" runat="server" Width="200px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Number of Rooms:</td>
<td>
<asp:TextBox ID="roomsTxtBox" runat="server" Width="80px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Monthly Rent:</td>
<td>
<asp:TextBox ID="rentTxtBox" runat="server" Width="80px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Period to list:</td>
<td><asp:DropDownList ID="listPeriodddl" runat="server"
DataSourceID="listPeriodSqlDataSource" DataTextField="listDays"
DataValueField="listDays" Width="85px">
</asp:DropDownList><asp:SqlDataSource ID="listPeriodSqlDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:PlaceConnectionString1 %>"
SelectCommand="SELECT [listDays] FROM [listPeriod]"></asp:SqlDataSource>
</td>
</tr>
<tr>
<td class="style2">
Additional Infomation:</td>
<td><asp:TextBox ID="addInfoTxtBox" runat="server" Rows="5" TextMode="MultiLine"
Width="200px"></asp:TextBox>
</td>
</tr>
</table>
<br />
;&nbsp;&nbsp;&nbsp;<asp:Button ID="insertSubmitBtn" runat="server" Text="Submit" Width="140px"
onclick="insertSubmitBtn_Click" />
<br/>
 <cc1:CascadingDropDown
ID="Provincecdd2"
runat="server"
TargetControlID="Provinceddl2"
Category="Province"
PromptText="Select a province"
ServicePath="Location.asmx"
ServiceMethod="GetProvince" />
<br /><cc1:CascadingDropDown
ID="Citycdd2"
runat="server"
TargetControlID="Cityddl2"
ParentControlID="Provinceddl2"
Category="City"
PromptText="Select a city"
ServicePath="Location.asmx"
ServiceMethod="GetCity" />
 
</div></form>
</body>
</html>
 
Server Error .


Invalid postback or callback argument.  Event validation is enabled using <pages enableEventValidation="true"/> in configuration or <%@ Page EnableEventValidation="true" %> in a page.  For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them.  If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.ArgumentException: Invalid postback or callback argument.  Event validation is enabled using <pages enableEventValidation="true"/> in configuration or <%@ Page EnableEventValidation="true" %> in a page.  For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them.  If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.Source Error:



An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:



[ArgumentException: Invalid postback or callback argument. Event validation is enabled using <pages enableEventValidation="true"/> in configuration or <%@ Page EnableEventValidation="true" %> in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.]
System.Web.UI.ClientScriptManager.ValidateEvent(String uniqueId, String argument) +2132728
System.Web.UI.Control.ValidateEvent(String uniqueID, String eventArgument) +108
System.Web.UI.WebControls.DropDownList.LoadPostData(String postDataKey, NameValueCollection postCollection) +55
System.Web.UI.WebControls.DropDownList.System.Web.UI.IPostBackDataHandler.LoadPostData(String postDataKey, NameValueCollection postCollection) +11
System.Web.UI.Page.ProcessPostData(NameValueCollection postData, Boolean fBeforeLoad) +353
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1194

 
Hotkwesi

View 1 Replies View Related

SQL Server 2008 :: Insert Data Into Table Variable But Need To Insert 1 Or 2 Rows Depending On Data

Feb 26, 2015

I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)

I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !

Below is the code i have at the moment

declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT

set @startdate = '2015-01-01'
set @enddate = '2015-01-31'

[Code] .....

View 1 Replies View Related

Adding Data From A Form To A Database

Nov 15, 2007

Very basic question here but I need to add a form to a page so it adds content, its as simple as that.  I've done this kind of using a <asp.Formview> tag and it works, but becuase I think I've used a form view I have to click add to insert data first.  Should I be using the <asp:formview> tag or is it a problem with the templates?
 <asp:FormView ID="FormView1" runat="server" DataKeyNames="ContentID" DataSourceID="ObjectDataSource1">                                      <InsertItemTemplate>                        Resource :<asp:DropDownList ID="ResourceIDTextBox" runat="server" Text='<%# Bind("ResourceID") %>' DataSourceID="ObjectDataSource2"                            DataTextField="Resource" DataValueField="ResourceID">                        </asp:DropDownList><br />                        Headline:                        <asp:TextBox ID="HeadlineTextBox" runat="server" Text='<%# Bind("Headline") %>' Width="451px"></asp:TextBox><br />                        Date:                        <asp:TextBox ID="DateTextBox" runat="server" Text='<%# Bind("Date") %>'>                        </asp:TextBox><br />                        Body:                                        <FTB:FreeTextBox id="FreeTextBox1" runat="Server" Text='<%# Bind("Body") %>' Width="542px" />                        <br />                        ImageURL:                        <asp:TextBox ID="ImageURLTextBox" runat="server" Text='<%# Bind("ImageURL") %>'>                        </asp:TextBox><br />                        OpeningPara:                        <asp:TextBox ID="OpeningParaTextBox" runat="server" Text='<%# Bind("OpeningPara") %>'>                        </asp:TextBox><br />                        Ret:                        <asp:TextBox ID="RetTextBox" runat="server" Text='<%# Bind("Ret") %>'>                        </asp:TextBox><br />                        Man:                        <asp:TextBox ID="ManTextBox" runat="server" Text='<%# Bind("Man") %>'>                        </asp:TextBox><br />                        Pro:                        <asp:TextBox ID="ProTextBox" runat="server" Text='<%# Bind("Pro") %>'>                        </asp:TextBox><br />                        Com:                        <asp:TextBox ID="ComTextBox" runat="server" Text='<%# Bind("Com") %>'>                        </asp:TextBox><br />                        IndustryID:                        <asp:TextBox ID="IndustryIDTextBox" runat="server" Text='<%# Bind("IndustryID") %>'>                        </asp:TextBox><br />                        ContentTitle:                        <asp:TextBox ID="ContentTitleTextBox" runat="server" Text='<%# Bind("ContentTitle") %>'>                        </asp:TextBox><br />                        Status:                        <asp:TextBox ID="StatusTextBox" runat="server" Text='<%# Bind("Status") %>'>                        </asp:TextBox><br />                        Pub:                        <asp:TextBox ID="PubTextBox" runat="server" Text='<%# Bind("Pub") %>'>                        </asp:TextBox><br />                        Fin:                        <asp:TextBox ID="FinTextBox" runat="server" Text='<%# Bind("Fin") %>'>                        </asp:TextBox><br />                        <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"                            Text="Insert">                        </asp:LinkButton>                        <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"                            Text="Cancel">                        </asp:LinkButton>                    </InsertItemTemplate>                    <ItemTemplate>                                             <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"                            Text="Click here to Add New Content">                        </asp:LinkButton>                    </ItemTemplate>                </asp:FormView>
                <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="Delete"                    InsertMethod="Insert" OldValuesParameterFormatString="original_{0}" SelectMethod="ContentADDMethod"                    TypeName="DataSet2TableAdapters.ContentADDAdapter" UpdateMethod="Update">                    <DeleteParameters>                        <asp:Parameter Name="Original_ContentID" Type="Int32" />                    </DeleteParameters>                    <UpdateParameters>                        <asp:Parameter Name="ResourceID" Type="Int32" />                        <asp:Parameter Name="Headline" Type="String" />                        <asp:Parameter Name="Date" Type="String" />                        <asp:Parameter Name="Body" Type="String" />                        <asp:Parameter Name="ImageURL" Type="String" />                        <asp:Parameter Name="OpeningPara" Type="String" />                        <asp:Parameter Name="Ret" Type="String" />                        <asp:Parameter Name="Man" Type="String" />                        <asp:Parameter Name="Pro" Type="String" />                        <asp:Parameter Name="Com" Type="String" />                        <asp:Parameter Name="IndustryID" Type="Int32" />                        <asp:Parameter Name="ContentTitle" Type="String" />                        <asp:Parameter Name="Status" Type="String" />                        <asp:Parameter Name="Pub" Type="String" />                        <asp:Parameter Name="Fin" Type="String" />                        <asp:Parameter Name="Original_ContentID" Type="Int32" />                        <asp:Parameter Name="ContentID" Type="Int32" />                    </UpdateParameters>                    <InsertParameters>                        <asp:Parameter Name="ResourceID" Type="Int32" />                        <asp:Parameter Name="Headline" Type="String" />                        <asp:Parameter Name="Date" Type="String" />                        <asp:Parameter Name="Body" Type="String" />                        <asp:Parameter Name="ImageURL" Type="String" />                        <asp:Parameter Name="OpeningPara" Type="String" />                        <asp:Parameter Name="Ret" Type="String" />                        <asp:Parameter Name="Man" Type="String" />                        <asp:Parameter Name="Pro" Type="String" />                        <asp:Parameter Name="Com" Type="String" />                        <asp:Parameter Name="IndustryID" Type="Int32" />                        <asp:Parameter Name="ContentTitle" Type="String" />                        <asp:Parameter Name="Status" Type="String" />                        <asp:Parameter Name="Pub" Type="String" />                        <asp:Parameter Name="Fin" Type="String" />                    </InsertParameters>                </asp:ObjectDataSource>                                                      <asp:ObjectDataSource ID="ObjectDataSource2" runat="server" InsertMethod="Insert"                      OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" TypeName="DataSet2TableAdapters.ResourcesTableAdapter">                      <InsertParameters>                          <asp:Parameter Name="Resource" Type="String" />                      </InsertParameters>                  </asp:ObjectDataSource>
 

View 1 Replies View Related

Updates Form Remote Data

Oct 13, 2005

I have been successful with DTS packages and various SQL statements. However, I have a new challenge. I have a table in an SQL Server database. One of the columns is employee number and a column for department number(which is not populated) In a remote AS400 file I have the employees number and department number. I want to create a package to connect to remote table and update SQL Server table with department number where the two tables match on the employee number.

View 3 Replies View Related

Using Query Or Vba ,transfer Data From One Form To

Feb 27, 2007

using a query or vba ,transfer data from one form to another,
table1 customers-form1 customer address,
table2 orders-form2 order address,
how do i transfer data from customer address to order address
yours M [/center]

View 1 Replies View Related

Simplest Form For Data Entry

Nov 20, 2007

I am researching ideas for the best way to have users enter data into SQL.

I am familiar with VB and Access Forms and HTML coding but what I am looking for is a way to have a standalone app or website where users can enter information but I want the form to have branches that change depending on user input (such as selecting a meeting type then having specific questions open up related to it).

Any ideas on the best way to do this?

If a different forum would be better for this request please advise and I will move appropriately.

Respectfully,

Lewis

View 2 Replies View Related

Designing Data Entry Form With Scope_identity .....help

Jan 17, 2007

hi i'm thinking of putting 3 texboxes for the user to enter their previous work exp which look something like this:
experienceFirst job.textboxSecondjob.textboxThirdjob.textbox
i'm kinda confuse on with the database. The 3 tables required means i will hav 3 sqldatasource ....which i didin't quite get it right with the Secondjob table.I try using identity_scope() and @@identity to get the ID from my first table(personel)which is autogenerate number. It works fine with Firstjob table but not secondjob table.
Generated error message:
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 6
The INSERT statement conflicted with the FOREIGN KEY constraint "Personnel_Secondjob". The conflict occurred in database "test", table "dbo.Personnel", column 'IDPersonnel'.
The statement has been terminated.
INSERT INTO dbo.Personnel
(Name)
VALUES ('Jon')
insert into Firstjob(Designation, Employer, YearWorking, IDPersonnel)
values('Teachier, 'Gam', '7thn', scope_identity())
insert into Secondjob(Designation, Employer, YearWorking, IDPersonnel)
values('Musician', 'KKSB', '8thn', scope_identity())
 any suggestions....

View 2 Replies View Related

Problem Storing Form Data Into Database

Oct 15, 2007

Hey all,         I have a form in one page and when the user clicks the submit button it has to save the data into my SQL database which i have created.It doesnt show any error and it successfully redirects to another page but not saving the data .Could someone help please.Here is my code under submit button         SqlDataSource txtDataSource = new SqlDataSource();        txtDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString();        txtDataSource.InsertCommandType = SqlDataSourceCommandType.Text;        txtDataSource.InsertCommand = "INSERT INTO Hamburgdata(user_name, report_type, company_name , street_address, city, state, zip_code, tax_ID) VALUES (@user_name,@report_type,@company_name ,@street_address,@city,@state,@zip_code,@Tax_ID)";        txtDataSource.InsertParameters.Add("user_name", User.Identity.Name);        txtDataSource.InsertParameters.Add("report_type", ReportType.Text);        txtDataSource.InsertParameters.Add("company_name", CompanyName.Text);        txtDataSource.InsertParameters.Add("street_address", StreetAddress.Text);        txtDataSource.InsertParameters.Add("city", City.Text);        txtDataSource.InsertParameters.Add("state", State.Text);        txtDataSource.InsertParameters.Add("zip_code", ZipCode.Text);        txtDataSource.InsertParameters.Add("tax_ID", TaxID.Text);        int rowAffected = 0;        try        {            rowAffected = txtDataSource.Insert();        }        catch (Exception exp)        {                    }        finally        {            txtDataSource = null;        }           }  ThanksVik 

View 7 Replies View Related

How To Display Data Using Stored Procedure In My Asp.net Form

Jul 29, 2004

I want to display the data in datagrid using the stored procedure,

Can you please tell me, how i can create the stored procedure for the following:
using select query(SELECT Top 10 OrderID, CustomerID, EmployeeID, OrderDate FROM Orders)

I want to display the stored procedure data in my Datagrid.

Thank you very much for the help.
the following is complete inline code on my webform.


Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("NorthwindConnection"))
Dim objCmd As New SqlCommand
Dim dataAdapter As SqlDataAdapter

objCmd.Connection = objConn
objCmd.CommandType = CommandType.Text
objCmd.CommandText = "SELECT Top 10 OrderID, CustomerID, EmployeeID, OrderDate FROM Orders"

objConn.Open()

dataAdapter = New SqlDataAdapter
dataAdapter.TableMappings.Add("Table", "Orders")
dataAdapter.SelectCommand = objCmd

dataSet = New DataSet("Orders")
dataAdapter.Fill(dataSet)
dtgOrders.DataSource = dataSet
dtgOrders.DataBind()

objConn.Dispose()

View 1 Replies View Related

Transfering Data From A Web Form Into Sql Server Database

Sep 20, 2005

Hi I am trying to develop a web based application. I am trying to insert data from my web form into sql server database using stored procedures. I am having a problem while i try to exceute the command. The error given by system is System.InvalidCastException: Object must implement IConvertible. at System.Data.SqlClient.SqlCommand.ExecuteNonQueryany suggestion what am i missing?With Regards,Sameer Jindal

View 2 Replies View Related

Export Data Form The Database To A Third Party. How?

Apr 9, 2001

Hi friends, I,m familiar with accessing data from a SQL DB using ASP. Lets say I have a cinema website with lots of info about upcoming shows, well, how do I export data form the database to a third party?? Lets say a local newspaper wants the show-time info. How do I get the data to them. Also how are different files
.xls etc,exported? Thanks for any help.
Meltdown
/////
~ ~
@ @
<
\__/

View 2 Replies View Related

Data Entry Form TO LOAD TO SQL ---- URGENT PLEASE????

Aug 25, 2003

Hi Guys,
I have a request to create a data entry form with validation. The data is in hardcopy and people have to enter data on the screen and then this data should be output to a CSV file, so that I can load that csv file to a table. One of other important requirement is that Data entry person should be able to view the data entered and she should be able to modify them aswell. I.E we should have a form where it should also display the entered data with modifyable feautre.
I know some VB, I thought about doing this in VB. Can anyone please SUGGEST ME HOW TO IMPLEMENT THIS or Any sample code?? I really appreciate for your Help and Input. Thanks

View 1 Replies View Related

Committing Data To SQL From A Visual Basic Form

Jun 12, 2007


I have novice level visual basic knowledge using Visual Studio 2005 and have little knowledge of SQL using SQL express. I am attempting to create a SQL database which initially consists of a single table bound to a Visual Basic form for data entry. So far I have been able to bind a VB form to a SQL database creating a DataSet, DataAdapter, and utilizing the default binding navigator. I also seem to have functioning Stored Procedures.

The VB form is able to add data to the SQL table and I can toggle through the data while the VB app is running but when I terminate the app and load it again all newly added data is not available from the SQL table.

I have seen other strings on this subject and viewed many tutorials but they only bring me as far as I am. None of them address the concept of permanently committing data to SQL from the VB form. FYI€¦data entered manually into the SQL table is stored, just not data from the form.

Does anyone have any idea what is wrong?

View 4 Replies View Related

InfoPath Form's Data To SQL / SSRS Database

Apr 11, 2008

Hi

In my current project we are dealing with a lot if Infopath forms of all sizes and complexities. currently they are being saved in Forms library of sharepoint.
We need to build a warehouse for SSRS which receives data either from content database or from infopath forms submit with minimum latency.
I thought of few alternatives, but, not sure which is most robust and economical.
1. Built c# classes to parse XML of infopath forms and then push data to SQL using ADO.net and SQL stored procedure while item is being added (ItemAdding) to Forms Library.
2. Use CAML queries to extract XML from forms library and then continue with C#/ADO/SQL..
3. Use SSIS APIs and webservices to massage XML and put it to SQL at ItemAdding event
4. Use CAML queries to generate XML files and stage it to FTP and rest will be done on SSIS.

I am currently looking for feseability information based of Besy Known Practise. Please feel free to suggest a totally new approach, if available.

Thanks

View 3 Replies View Related

Transact SQL :: How To Add Column Having Varchar Data In Form Hh:mm:ss

May 25, 2015

There is a column named Timings in HH:MM:SS format. Datatype of this column is varchar(50).

I want to sum the rows in this column and get the output as one single record.

00:01:06
00:01:16
00:01:04
00:01:24
00:01:13
00:01:06
00:02:21
00:01:16

View 4 Replies View Related

Saving Data To A Database File Via Simple Form

Nov 2, 2007

Hi,
I am designing a site using Visual Web Developer, CSharp and Sql server Express.
One the contact page I want to put a form that allows users to enter details about
themselves. On clicking the button this will be stored in the database under a table
called subscribers. The form will have, name, address, telephone, email fields etc.
With the email addresses from the visitors I want to be able to keep them in
a newsletter section or similar which is automated so they recevie emails from time to time
Could somebody suggest a tutorial which shows how to complete this process
using c sharp and sql.
 
Thanks for you time
 
Prontonet
 

View 2 Replies View Related

Making A Form That Inputs Data In To My Database And Getting This Error...

Jan 5, 2008

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: BC30456: 'InserParameters' is not a member of 'System.Web.UI.WebControls.SqlDataSource'.Source Error:





Line 15: registrationDataSource.InsertCommand = "INSERT TO Reputation (firstname, lastname)VALUES(@First Name, @Last Name)"
Line 16:
Line 17: registrationDataSource.InserParameters.Add("firstname", firstname.txt)
Line 18: registrationDataSource.InserParameters.Add("lastname", lastname.txt)
Line 19: Source File: C:UsersQaiphyx
eputationDefault.aspx.vb    Line: 17
Show Detailed Compiler Output:

View 1 Replies View Related

Inserting Data In SQL 2005 Using Javascript From A Form Object

Mar 4, 2008

I am new to ASP.NET. At present i am developing a web application in which i need to insert data in database(MS SQL 2005) from a form which uses only HTML controls. I need to use HTML controls so posting of form to the server is not done and hence i need to generate HTML controls using javascript. So the data in the form must be stored in the database. How this data can be inserted in the database is the problem and i think that javascript might be the solution for it.
 
Sagar 

View 3 Replies View Related

On Formview Clicking Update Causes Form Data To Disappear?!?

Mar 24, 2008

Follow-up to:
http://forums.asp.net/t/1237676.aspx
The update command seems to be deleting my data.  If I set the Parameter "DefaultValue" to "NULL" then it updates that field to the literal string: NULL
WTH
Nick
<UpdateParameters>
<asp:Parameter Name="DevelopmentArea" Type="String"/>
<asp:Parameter Name="DevelopmentGoals" Type="String" />
<asp:Parameter Name="DevelopmentPlans" Type="String" />
<asp:Parameter Name="CurrentStatus" Type="String" />
<asp:Parameter Name="FutureState" Type="String" />
<asp:Parameter Name="DetermineFactor" Type="String"/>
<asp:Parameter Name="MentorRequested" Type="Boolean" DefaultValue="False"/>
<asp:Parameter Name="MentorList" Type="String"/>
<asp:Parameter Name="CheckPointDate" Type="DateTime" />
<asp:Parameter Name="DateUpdated" Type="DateTime" />
<asp:Parameter Name="PlanPKID" Type="Int32" />
</UpdateParameters>

View 6 Replies View Related

SQL 2012 :: Design To Store Different Forms And Form Data?

Oct 17, 2014

I am looking to store the different forms and data in our database. We have several different forms and contains different information. I am looking for different approaches to model this table structure.

Also, I need to make sure the table structure will allow for new forms.

View 5 Replies View Related

Retrieve Data Form A Country Combined With 3 To 4 Cities

Feb 12, 2015

This Question is pertaining to AND and OR operators.

If we want to retrieve data form a country combined with 3 to 4 cities how do we handle this?

Say for ex:- i want to retrieve all data from Customers table where country is Germany and cities are Berlin, Mannheim,Brandenburg and München.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved