triggering SQLDATASOURCE insert or update command programatically?

Is there any way to initiate a sqldatasource insert from code in place
of the button with commandname=insert or insert?
I have a formview with a datasource with an insert command on a
button. When the button is selected the insert is performed.
Say I have another button on the form with no command syntax that calls
a some sub. Is there any way to call the same sqldatasource insert
function from code? in debug when I look at the onclick sub that I am
calling when the insert button is pressed, i see all the properties of
the button, like commandname=insert, but that sub is not the real deal
and is infact not even doing anything.
Thanks.Hi,
Have you tried calling the Insert method of the sqldatasource directly, it
should do what you want
Regards,
Mohamed Mosalem
http://mosalem.blogspot.com
"Jason" wrote:

> Is there any way to initiate a sqldatasource insert from code in place
> of the button with commandname=insert or insert?
> I have a formview with a datasource with an insert command on a
> button. When the button is selected the insert is performed.
> Say I have another button on the form with no command syntax that calls
> a some sub. Is there any way to call the same sqldatasource insert
> function from code? in debug when I look at the onclick sub that I am
> calling when the insert button is pressed, i see all the properties of
> the button, like commandname=insert, but that sub is not the real deal
> and is infact not even doing anything.
> Thanks.
>
somebody else suggested that so I tried it, but when I do that for some
reason it does not see bound fields. I had two buttons up, one calling
a sub that did what you suggested and another with no code behind doing
commandname=insert and the only the later would work.
Mohamed Mosalem wrote:
> Hi,
> Have you tried calling the Insert method of the sqldatasource directly, it
> should do what you want
> Regards,
> Mohamed Mosalem
> http://mosalem.blogspot.com
> "Jason" wrote:
>

triggering SQLDATASOURCE insert or update command programatically?

Is there any way to initiate a sqldatasource insert from code in place
of the button with commandname=insert or insert?

I have a formview with a datasource with an insert command on a
button. When the button is selected the insert is performed.

Say I have another button on the form with no command syntax that calls
a some sub. Is there any way to call the same sqldatasource insert
function from code? in debug when I look at the onclick sub that I am
calling when the insert button is pressed, i see all the properties of
the button, like commandname=insert, but that sub is not the real deal
and is infact not even doing anything.

Thanks.Hi,
Have you tried calling the Insert method of the sqldatasource directly, it
should do what you want

Regards,
Mohamed Mosalem
http://mosalem.blogspot.com
"Jason" wrote:

Quote:

Originally Posted by

Is there any way to initiate a sqldatasource insert from code in place
of the button with commandname=insert or insert?
>
I have a formview with a datasource with an insert command on a
button. When the button is selected the insert is performed.
>
Say I have another button on the form with no command syntax that calls
a some sub. Is there any way to call the same sqldatasource insert
function from code? in debug when I look at the onclick sub that I am
calling when the insert button is pressed, i see all the properties of
the button, like commandname=insert, but that sub is not the real deal
and is infact not even doing anything.
>
Thanks.
>
>

somebody else suggested that so I tried it, but when I do that for some
reason it does not see bound fields. I had two buttons up, one calling
a sub that did what you suggested and another with no code behind doing
commandname=insert and the only the later would work.
Mohamed Mosalem wrote:

Quote:

Originally Posted by

Hi,
Have you tried calling the Insert method of the sqldatasource directly, it
should do what you want
>
Regards,
Mohamed Mosalem
http://mosalem.blogspot.com
>
"Jason" wrote:
>

Quote:

Originally Posted by

Is there any way to initiate a sqldatasource insert from code in place
of the button with commandname=insert or insert?

I have a formview with a datasource with an insert command on a
button. When the button is selected the insert is performed.

Say I have another button on the form with no command syntax that calls
a some sub. Is there any way to call the same sqldatasource insert
function from code? in debug when I look at the onclick sub that I am
calling when the insert button is pressed, i see all the properties of
the button, like commandname=insert, but that sub is not the real deal
and is infact not even doing anything.

Thanks.

Trouble inserting items from a dropdownlist into a database

Hi I'm relatively new to asp and I'm having a bit of trouble on one of my projects.

Basically I can't work out how to insert an item, that's been selected from a dropdownlist, into a table. I tried:

string queryString; queryString = "Insert INTO Details(Gender) VALUES(";

queryString += "'" + list1.SelectedItem.Value + "')";

However this didn't seem to work and I was wondering what bit of code I had to change to make it insert the selected item from the dropdownlist (list1)

Thanks!

Try list1.SelectedValue.

You should also use parameters instead of concatenating to the end of your SQL string. This leaves your application open for SQL injection attacks.

sivilian

Trouble using parameters to insert database record

I am having a hell of a time trying to insert a database record into an SQL Database. I am using VS.NET to generate this code, I am not using a dataset. I get the following error:

System.FormatException: Input string was not in a correct format.

I have narrowed that error down to that error down to the int fields, how do I convert the string from the form into an int (I tried Convert.ToInt32() without success)?

I can get the insert the data successfully if use an explicit value (Value = 30) and not (Value = Formfield.Text)

Also how do I get the DB KeyID Field to autogenerate a number, currently if I do not manually enter a value throws a no Null value allowed error?

-------------------------------
this.sqlInsertCommand1.CommandText = @dotnet.itags.org."INSERT INTO Events(KeyID, EventDateTime, EventName, StartLocation, EndLocation, AvailableSeats, Description, RateAdult, RateChild, RateSenior) VALUES (@dotnet.itags.org.KeyID, @dotnet.itags.org.EventDateTime, @dotnet.itags.org.EventName, @dotnet.itags.org.StartLocation, @dotnet.itags.org.EndLocation, @dotnet.itags.org.AvailableSeats, @dotnet.itags.org.Description, @dotnet.itags.org.RateAdult, @dotnet.itags.org.RateChild, @dotnet.itags.org.RateSenior); SELECT KeyID, EventDateTime, EventName, StartLocation, EndLocation, AvailableSeats, Description, RateAdult, RateChild, RateSenior, EventID FROM Events WHERE KeyID = @dotnet.itags.org.KeyID";

this.sqlInsertCommand1.Connection = this.sqlConnection1;
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.KeyID", System.Data.SqlDbType.Int, 4);
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.EventDateTime", System.Data.SqlDbType.SmallDateTime, 4).Value = System.DateTime.Now;
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.AvailableSeats", System.Data.SqlDbType.Int, 4).Value = 21;
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.EndLocation", System.Data.SqlDbType.NVarChar, 50).Value = EndLocation.Text;
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.EventName", System.Data.SqlDbType.NVarChar, 50).Value = EventName.Text;
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.RateAdult", System.Data.SqlDbType.Int, 4).Value = RateAdult.Text;
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.RateChild", System.Data.SqlDbType.Int, 4).Value = RateChild.Text;
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.RateSenior", System.Data.SqlDbType.Int, 4).Value = RateSenior.Text;
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.StartLocation", System.Data.SqlDbType.NVarChar, 50).Value = StartLocation.Text;
this.sqlInsertCommand1.Parameters.Add("@dotnet.itags.org.Description", System.Data.SqlDbType.Text, 16).Value = Description.Text;

Thanks, Justin.Example on how to parse a text (must of course be number only entry, and cannot be an empty string) into an Integer:

sqlInsertCommand1.Parameters.Add("@.RateAdult", SqlDbType.Int, 4).Value = int.Parse(RateAdult.Text);

You should of course not set the KeyID field manually. This is taken care of by the DataBase when designing your Table:

Identity = Yes
Identity Seed = 1
Identity Increment = 1

Thanks Andre,

I have set the key ID field to "Identity = Yes" but I am not not sure what to do in the code with the KeyID Value. If I do not declare a value I get a non-null error.

Any ideas?

Can you recommend some good articles/tutorials for adding data to an MS SQL DB?

Thanks, Justin.
When inserting you need not worry about the KeyID value as it is created when the data is inserted into the DataBase. When updating or deleting (or selecting a certain record based on an ID) you need to get the ID value from Database when you process your query to get the Data you need to display.

If you are using a DataGrid or a DataList to present the Data, you'll use the the DataKeyField property of the DataGrid/DataList (the Repeater Control does not have this Property) to store the id:

DataKeyField = "KeyID"

When you update (or delete), you'll get the necessary ID like this:

int keyID = (int)myDataGrid.DataKeys[e.Item.ItemIndex];

Now you have the correct id for updating or deleting the correct record.
Thanks for the help.

As instructed by your first reply I tried using int.Parse() but received the same error I added an if statement to make sure the form variable was not empty.

Also, I removed all traces of keyID from the insert statment but I am still getting no nulls allowed error.

Thank you for your patience as I am still learning. I appreciate the guidance.

Thanks, Justin.
You will need to show us your Table design. What are the fields of the Table you are trying to insert into? What Type of data do they accept, do they accept 'null' (can they be empty) values or not? Remember that a 'null' value is NOT the same thing as an empty string ("").

"I can get the insert the data successfully if use an explicit value (Value = 30) and not (Value = Formfield.Text)"

If 'Formfield.Text' is empty, you cannot parse it using 'int.Parse(Formfield.Text)'. That will give you an exception. What you can do is e.g. use:

int formfield = 0;
if (Formfield.Text.Trim().Length > 0)
 formfield = int.Parse(Formfield.Text.Trim());
sqlInsertCommand1.Parameters.Add("@.someValue", System.Data.SqlDbType.Int, 4).Value = formfield;

This way you ensure that if the particular formfield has not been assigned a value by the user, the value will be '0'.

If there are fields in your Table that by design do not accept 'null' values, you must insert some kind of value. Either by setting a default value in your table design, or by making sure that a valid value is entered by the User, using .Net Validator Controls.
Well, I finally fixed the problem.

VS.NET puts the insert statement in the InitializeComponent() class and I thought if I put the execute command in submit's event handeler it would execute the insert statement from the InitializeComponent() but it wasn't.

I put the insert command in the event handeler and now it's working.

Thank you very much for your patience.

Thanks, Justin.

Troubles with INSERT command

Hi, I am currently trying to INSERT selected rows from table Cart intotable Orders. Table Cart has columns Id_record(autonumber),Id_title(LongInt), Customer(text), Table Orders has the same columnsand also column Order_ID. When I delete this colums Order_ID and insertonly values from table cart like this, it works.
queryString = "INSERT INTO [Orders] SELECT * FROM [Cart] WHERE Cart.Customer = @dotnet.itags.org.user"
Dim dbCommand as OleDbCommand = new OleDbCommand(queryString, myConnection)
dbCommand.Parameters.Add(New OleDbParameter("@dotnet.itags.org.user", OleDbType.Char, 20))
dbCommand.Parameters("@dotnet.itags.org.user").Value = user.Identity.Name

Troubles begin, when I try to insert also that randomly generated number into my table Orders. I use this code...
Dim myConnection as OleDbConnection = new OleDbConnection(connectionString)
queryString = "INSERT INTO [Orders] SELECT *, @dotnet.itags.org.random as Order_ID FROM [Cart] WHERE Cart.Customer = @dotnet.itags.org.user"
Dim dbCommand as OleDbCommand = new OleDbCommand(queryString, myConnection)
dbCommand.Parameters.Add(New OleDbParameter("@dotnet.itags.org.user", OleDbType.Char, 20))
dbCommand.Parameters("@dotnet.itags.org.user").Value = user.Identity.Name
Randomize()
dbCommand.Parameters.Add(New OleDbParameter("@dotnet.itags.org.random", OleDbType.Integer, 20))
dbCommand.Parameters("@dotnet.itags.org.random").Value = ((Int32.MaxValue) * Rnd)
I always ger error "Duplicate output destination in Id_record" andhave no idea, how to fix it. Could pls somebody help me? Or could ugive me some link on some good INSERT tutorial?

If both the tables are not EXCTLY similar in columns you would get errors. So list out the column names xplicitly.
INSERT INTO col1, col2, col3 SELECT cart.col1, cart,col2, cart.col3 FROM cart WHERE Cart.Customer = @.user"

Exclude the identity column since you cannot manually insert into it.

Hm, so I changed my query into something like this, but now im gettingOverflow error. Does someone know what Overflow error means? Could bethis error caused bz the fact, that Id_record is autonumber in Carttable but I am trying to isnert it as integer in Orders table?

Dim myConnection as OleDbConnection = new OleDbConnection(connectionString)
queryString = "INSERT INTO [Orders] (Id_record,Id_title, Customer, Id_order) SELECT Id_record, Id_title, Customer,@.user FROM [Cart] WHERE Cart.Customer = @.user"
Dim dbCommand as OleDbCommand = new OleDbCommand(queryString, myConnection)
Randomize()
Dim Rand As Int32 = ((Int32.MaxValue) * Rnd)
dbCommand.Parameters.Add(New OleDbParameter("@.random", OleDbType.Integer, 20))
dbCommand.Parameters("@.random").Value = Rand
dbCommand.Parameters.Add(New OleDbParameter("@.user", OleDbType.Char, 20))
dbCommand.Parameters("@.user").Value = user.Identity.Name


queryString = "INSERT INTO [Orders] (Id_record, Id_title, Customer, Id_order) SELECT Id_record, Id_title, Customer, @.user FROM [Cart] WHERE Cart.Customer = @.user"

is Orders.Id_order column a autonumber column? if so you cant explicity insert into this column.
providing the sqldatatype(integer?) of Cart.Id_record is the same type as Orders.ID_record it shouldnt be a problem.
Well, Cart.ID_Record is the autonumber and Orders.ID_record is theinteger. I need to insert this autonumber into Orders.Id_record asinteger.
Orders.Id_order is normal Integer.
Edit: Hm, when i remove the last column from orders and try to insertjust first 3 columns, everything works fine. So the problem is either in the declaration of random variable or in the was it is placed in SQO query.

queryString = "INSERT INTO [Orders] (Id_record, Id_title, Customer, Id_order) SELECT Id_record, Id_title, Customer,@.user FROM [Cart] WHERE Cart.Customer = @.user"

whats with the @.user in the select list?
waht are you trying to do there with it?

Troubles with INSERT INTO Statement...

hey all, im currently trying to do an insert statement, using textbox's and a variable. the only thing is its inserting txtTextBox.Text into the db instead of the value of that textbox...and i get this error when trying to insert my variable...

Exception Details: System.Data.SqlClient.SqlException: The name 'RndmNum' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

Source Error:

Line 42: dbQuery = "INSERT INTO table (name, fault_type, fault_location, fault_description, fault_number) VALUES ('txtName.Text', 'txtType.Text', 'txtLocation.Text', 'txtDesc.Text', RndmNum)"
Line 43: dbCommand = New SqlCommand(dbQuery,dbConnection)
Line 44: dbCommand.ExecuteNonQuery()
Line 45: dbConnection.Close
Line 46:

this is my insert statement:

dbQuery = "INSERT INTO table (name, fault_type, fault_location, fault_description, fault_number) VALUES ('txtName.Text', 'txtType.Text', 'txtLocation.Text', 'txtDesc.Text', RndmNum)"

thanks everyone,
JustinYou are sending txtName.Text as a string. Your dbQuery needs to look like this:

dbQuery = "INSERT INTO table (name, fault_type, fault_location, fault_description, fault_number) VALUES ('" & txtName.Text & "', '" & txtType.Text & "', '" & txtLocation.Text & "', '" & txtDesc.Text & "', " & RndmNum & ")"