Insert to a sql database from a details view!?!?!?!?

I have tried just about everything i could find on the internet to fix this.

Anyway....I have a sql databasewith 3 colums: Client ID, Client Name, and Client Address

I have no trouble reading the database through the gridview, but when i try to add a script that allows me to add to that database off of my site, I get error after error.

Anyone who can figure this out must be a sql genius (or I am a complete idoit).

Here is the code I have so far:

<%

@.PageLanguage="VB"MasterPageFile="~/MasterPage.master" %>

<

asp:ContentID="Content1"runat="server"ContentPlaceHolderID="ContentPlaceHolder1"><scriptrunat="server">Dim strClient_NameAsStringDim strClient_AddressAsString</script><asp:DetailsViewID="DetailsView1"runat="server"AutoGenerateRows="False"DataKeyNames="Client ID"DataSourceID="SqlDataSource1"Height="50px"Width="125px"><Fields><asp:BoundFieldDataField="Client Name"HeaderText="Client Name"SortExpression="Client Name"/><asp:BoundFieldDataField="Client Address"HeaderText="Client Address"SortExpression="Client Address"/><asp:BoundFieldDataField="Client ID"HeaderText="Client ID"InsertVisible="False"ReadOnly="True"SortExpression="Client ID"/><asp:CommandFieldShowInsertButton="True"/></Fields></asp:DetailsView><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:clientInfoConnectionString %>"InsertCommand="INSERT INTO [Clients] ([Client Name], [Client Address]) VALUES (strClient_Name, strClient_Address)"SelectCommand="SELECT [Client Name], [Client Address], [Client ID] FROM Clients"></asp:SqlDataSource>

</

asp:Content>

Best Regards and Thank You,

The King

I thisnk that this is not correct

InsertCommand="INSERT INTO [Clients] ([Client Name], [Client Address]) VALUES (strClient_Name, strClient_Address)"

These

Dim strClient_NameAsString
Dim strClient_AddressAsString

Are variables not available for SQL INSERT Command, you have to modify your code see example on msdn:

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.insertcommand.aspx

The following code example demonstrates how to insert data into a database using theSqlDataSource control and a simple Web Forms page. The current data in the Data table is displayed in theDropDownList control. You can add new records by entering values into theTextBox controls, and then clicking theInsert button. When theInsert button is clicked, the specified values are inserted into the database, and theDropDownList is refreshed.

<%@.Page Language="VB" %><!DOCTYPE htmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server">PrivateSub InsertShipper (ByVal SourceAsObject,ByVal eAs EventArgs) SqlDataSource1.Insert()EndSub' InsertShipper</script><html > <head runat="server"> <title>ASP.NET Example</title></head><body> <formid="form1" runat="server"> <asp:dropdownlistid="DropDownList1" runat="server" datasourceid="SqlDataSource1" datatextfield="CompanyName" datavaluefield="ShipperID" /><!-- Security Note: The SqlDataSource uses a FormParameter, Security Note: which doesnot perform validationof input from the client. Security Note:To validate the valueof the FormParameter, handle the Insertingevent. --><asp:sqldatasourceid="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:MyNorthwind %>" selectcommand="SELECT CompanyName,ShipperID FROM Shippers" insertcommand="INSERT INTO Shippers (CompanyName,Phone) VALUES (@.CoName,@.Phone)"> <insertparameters> <asp:formparameter name="CoName" formfield="CompanyNameBox" /> <asp:formparameter name="Phone" formfield="PhoneBox" /> </insertparameters> </asp:sqldatasource> <br /><asp:textboxid="CompanyNameBox" runat="server" /> <asp:RequiredFieldValidatorid="RequiredFieldValidator1" runat="server" ControlToValidate="CompanyNameBox" Display="Static" ErrorMessage="Please enter a company name." /> <br /><asp:textboxid="PhoneBox" runat="server" /> <asp:RequiredFieldValidatorid="RequiredFieldValidator2" runat="server" ControlToValidate="PhoneBox" Display="Static" ErrorMessage="Please enter a phone number." /> <br /><asp:buttonid="Button1" runat="server" text="Insert New Shipper" onclick="InsertShipper" /> </form> </body></html>

|||

I tried to define the variable using:

PublicProperty InsertCommandAsString

like the article said to, and i got the error:

Incorrect syntax near 'nvarchar'.
The name "strClient_Name" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

any further help wold be appreciated.

|||

well, i looked a little deeper into the example code and saw some reason.

based off of this I rewrote my page, but i am using the exact code in the <script> tag.

it looks like it should work but every tie it brings back a syntax error when tested.

Is this something I did wrong?

Code Below:

//begin problem section

<

scriptrunat="server">

Private

Sub InsertShipper (ByVal SourceAsObject,ByVal eAs EventArgs)

SqlDataSource1.Insert()

End

Sub' InsertShipper

</

script>

//past here works perfectly

<asp:ContentID="Content1"runat="server"ContentPlaceHolderID="ContentPlaceHolder1">

<asp:sqldatasourceid="SqlDataSource1"runat="server"connectionstring="<%$ ConnectionStrings:clientInfoConnectionString %>"selectcommand="SELECT Client Name,Client Address FROM Clients"insertcommand="INSERT INTO Clients (Client Name,Client Address) VALUES (@.CName,@.CAddress)"><insertparameters><asp:formparametername="CName"formfield="Namebox"/><asp:formparametername="CAddress"formfield="Addressbox"/></insertparameters></asp:sqldatasource> Name:<br/><asp:TextBoxID="Namebox"runat="server"/><br/>

<asp:RequiredFieldValidatorID="RequiredFieldValidator1"runat="server"ControlToValidate="Namebox"ErrorMessage="Please Enter A Name"></asp:RequiredFieldValidator>

<br/>Address:<br/><asp:TextBoxID="Addressbox"runat="server"/><br/>

<asp:RequiredFieldValidatorID="RequiredFieldValidator2"runat="server"ControlToValidate="Addressbox"ErrorMessage="Please Enter An Address"></asp:RequiredFieldValidator>

<br/><asp:ButtonID="Submitbtn"runat="server"Text="Submit"OnClick="InsertShipper"/><br/>

</

asp:Content>

Thanks for any help in advance,

The King

|||

well, i looked a little deeper into the example code and saw some reason.

based off of this I rewrote my page, but i am using the exact code in the <script> tag.

it looks like it should work but every tie it brings back a syntax error when tested.

Is this something I did wrong?

Code Below:

//begin problem section

<

scriptrunat="server">

Private

Sub InsertShipper (ByVal SourceAsObject,ByVal eAs EventArgs)

SqlDataSource1.Insert()

End

Sub' InsertShipper

</

script>

//past here works perfectly

<asp:ContentID="Content1"runat="server"ContentPlaceHolderID="ContentPlaceHolder1">

<asp:sqldatasourceid="SqlDataSource1"runat="server"connectionstring="<%$ ConnectionStrings:clientInfoConnectionString %>"selectcommand="SELECT Client Name,Client Address FROM Clients"insertcommand="INSERT INTO Clients (Client Name,Client Address) VALUES (@.CName,@.CAddress)"><insertparameters><asp:formparametername="CName"formfield="Namebox"/><asp:formparametername="CAddress"formfield="Addressbox"/></insertparameters></asp:sqldatasource> Name:<br/><asp:TextBoxID="Namebox"runat="server"/><br/>

<asp:RequiredFieldValidatorID="RequiredFieldValidator1"runat="server"ControlToValidate="Namebox"ErrorMessage="Please Enter A Name"></asp:RequiredFieldValidator>

<br/>Address:<br/><asp:TextBoxID="Addressbox"runat="server"/><br/>

<asp:RequiredFieldValidatorID="RequiredFieldValidator2"runat="server"ControlToValidate="Addressbox"ErrorMessage="Please Enter An Address"></asp:RequiredFieldValidator>

<br/><asp:ButtonID="Submitbtn"runat="server"Text="Submit"OnClick="InsertShipper"/><br/>

</

asp:Content>

Thanks for any help in advance,

The King

|||

well, i looked a little deeper into the example code and saw some reason.

based off of this I rewrote my page, but i am using the exact code in the <script> tag.

it looks like it should work but every tie it brings back a syntax error when tested.

Is this something I did wrong?

Code Below:

//begin problem section

<

scriptrunat="server">

Private

Sub InsertShipper (ByVal SourceAsObject,ByVal eAs EventArgs)

SqlDataSource1.Insert()

End

Sub' InsertShipper

</

script>

//past here works perfectly

<asp:ContentID="Content1"runat="server"ContentPlaceHolderID="ContentPlaceHolder1">

<asp:sqldatasourceid="SqlDataSource1"runat="server"connectionstring="<%$ ConnectionStrings:clientInfoConnectionString %>"selectcommand="SELECT Client Name,Client Address FROM Clients"insertcommand="INSERT INTO Clients (Client Name,Client Address) VALUES (@.CName,@.CAddress)"><insertparameters><asp:formparametername="CName"formfield="Namebox"/><asp:formparametername="CAddress"formfield="Addressbox"/></insertparameters></asp:sqldatasource> Name:<br/><asp:TextBoxID="Namebox"runat="server"/><br/>

<asp:RequiredFieldValidatorID="RequiredFieldValidator1"runat="server"ControlToValidate="Namebox"ErrorMessage="Please Enter A Name"></asp:RequiredFieldValidator>

<br/>Address:<br/><asp:TextBoxID="Addressbox"runat="server"/><br/>

<asp:RequiredFieldValidatorID="RequiredFieldValidator2"runat="server"ControlToValidate="Addressbox"ErrorMessage="Please Enter An Address"></asp:RequiredFieldValidator>

<br/><asp:ButtonID="Submitbtn"runat="server"Text="Submit"OnClick="InsertShipper"/><br/>

</

asp:Content>

Thanks for any help in advance,

The King

|||

Hi,

When your object names contain spaces, you have to quote them with []. So, your insert command should look like

INSERT INTO Clients ([Client Name],[Client Address]) VALUES (@.CName,@.CAddress)">

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Insert Time into SQL server

Hi,

I have created a wizard form to collect user information.When use click Finish button all the details added to the SQL server database.
That part is ok.
But my problem is this

Through my interface I am giving user to select the date and time.
(I have used AJAX datepicker and MaskedEdit control)

After user type the date (Normal format is - HH:MM:SS) and click finish button I check the database.

It automatically Inserted the date also (Jan 1 1900)

So I dont want this date part and I just want Time only.How do I do this ??

(I have used datatype as : nvarchar instead of datetime .Because of if i use datatime it automatically inserts both data and time values)

Values with thedatetimedata type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after thebase date, January 1, 1900. The base date is the system reference date. Values fordatetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

So in your case the first 4 bytes are not used. By default the value of Jan 1 1900 is displayed.

I do suggest that you continue to use the datetime value to store your time for filtering and sorting purposes but when you display the data to the end user you simply format the data to a time format.

Hope this helps!

|||

Hi,

you can convert your datetime with the CONVERT command, e.g. (in Query Analyzer):

Declare @.input As DateTime
Set @.input = Getdate()

Select Convert(nvarchar,@.input,108)

Regards
Marc André

|||

Hi,

thanks for your reply.