CODEWITHSUNDEEP
c#.netado.net
Rajaram Shelar
Rajaram Shelar

Reputation: 7877

Store 'Sql Query Text' in datatbase

I want to store sql query in a database table column. I want to do the dynamic controls and dynamic data sources. When I put query in textbox and execute it give me an error Input string was not in a correct format. Below is my code.

thisCommand.CommandText = "Update Allowance_FormFields set LabelName='" +
   controlText + "', ControlTypeId=" + 
   Convert.ToInt32(ddlControlTypes.SelectedValue) + ", ControlOrder=" + 
   txtControlOrder.Text + ", ControlDataSize=" + 
   Convert.ToInt32(ddlControlDataLength.SelectedValue) + "," +
   "ControlData='" + txtControlData.Text + "', AllowanceId=" +  
   Convert.ToInt32(ddlAllowances.SelectedValue) +                 
   "VisibleTo=" + Convert.ToInt32(ddlVisibleTo.SelectedValue) +
   " Where FormFieldId=" + hdnFormControlId.Value + "";

I use query select top 2 employeeid,firstname from employees in txtControlData.Text. How to use that.

Upvotes: 1

Views: 112

Answers (4)

Sudhakar Tillapudi
Sudhakar Tillapudi

Reputation: 26209

Problem : You are missing Conversion for column ControlOrder, so the column ControlOrder will be treated as String and check for single quotes around it and as they are missing it will complain.

Solution : if the Column ControlOrder is INT type plese do the conversion as you ar doing for other columns.

Try This: but i don't suggest this

thisCommand.CommandText = "Update Allowance_FormFields set LabelName='" +
            controlText + "',ControlTypeId=" + Convert.ToInt32(ddlControlTypes.SelectedValue) + ",ControlOrder=" + Convert.ToInt32(txtControlOrder.Text) +",ControlDataSize=" + Convert.ToInt32(ddlControlDataLength.SelectedValue) + "," +
            "ControlData='" + txtControlData.Text + "',AllowanceId=" + Convert.ToInt32(ddlAllowances.SelectedValue) +
            "VisibleTo=" + Convert.ToInt32(ddlVisibleTo.SelectedValue) +" Where FormFieldId=" + hdnFormControlId.Value + "";

Suggestion : your query is open to SQL injection attacks, i would Strongly suggest you to use parameterised sql queries to avoid them.

Note : if you use parameterised SQL queries you can ignore conversions as those parameters will be send with required type implicitly.

Try This: using parameterised SQL queries 

thisCommand.CommandText=thisCommand.CommandText = "Update Allowance_FormFields set LabelName=@LabelName,ControlTypeId=@ControlTypeId,ControlOrder=@ControlOrder,ControlDataSize=@ControlDataSize,ControlData=@ControlData,AllowanceId=@AllowanceId,VisibleTo=@VisibleTo             Where FormFieldId=@FormFieldId";
thisCommand.Parameters.AddWithValue("@LabelName",controlText);
thisCommand.Parameters.AddWithValue("@ControlTypeId",ddlControlTypes.SelectedValue);
thisCommand.Parameters.AddWithValue("@ControlOrder",txtControlOrder.Text);
thisCommand.Parameters.AddWithValue("@ControlDataSize",ddlControlDataLength.SelectedValue);
thisCommand.Parameters.AddWithValue("@ControlData",txtControlData.Text);
thisCommand.Parameters.AddWithValue("@AllowanceId",ddlAllowances.SelectedValue);
thisCommand.Parameters.AddWithValue("@VisibleTo",ddlVisibleTo.SelectedValue);
thisCommand.Parameters.AddWithValue("@FormFieldId",hdnFormControlId.Value);

Upvotes: 3

Krishna
Krishna

Reputation: 170

A few pointers : 
1) Why dont you use stored procedures to perform these kind of operations
2) The answer proposed by Sudhakar is a good option. 
3) In case, if you still want to use the your original method to update database, then make sure the entire commandtext value is of string data type. I see that there is a combination of string and integer values. 
4) You may use apostrophe to bifurcate the values. It is easier to keep track of the string.
5) You may use string builder to create the entire commandtext string.

Upvotes: 0

Troy Jennings
Troy Jennings

Reputation: 432

For once, the answer really is: Parameterise your command string. You should be doing that anyway, but it will make it much easier to find where you've made mistakes in your command string, such as leaving out quotes.

Upvotes: 0

Grant Winney
Grant Winney

Reputation: 66439

This part may be the issue (no apostrophes around the value):

ControlOrder=" + txtControlOrder.Text

I'd suggest you write the whole thing out, substituting sample values in for the all the TextBox values, etc. You may come across other typos as well.

Upvotes: 0

Related Questions