Insertion of Data

I am using vs2005 and sqlserver2000My problem is i have 5 checkboxes and some textboxes.In this user selects the checkbox and textboxes dynamically .In this user selects one or two or three or when user selects header checkbox then all checkboxes are selected.And in my database i mentioned a single column for group of checkboes.So how should i insert the data into databasedim con as new sqlconnection("userid=sa;pwd=; initial catalog=test")dim cmd as sql commandProtected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Clickcmd.CommandText = "insert into check values(" what should i write here ...'" & text.text &"','" @. text2.text "'... ")con.open()cmd.executenonquery()con.close()End Subyour insert will majorly depend on the structure of the database.....how r u storing the it in the database...as bits or strings ......be a little more elaborate about the database structure.|||

It looks like you have one column for check boxes, and then one column each for all of the text fields? This depends on that you want to put in the checkbox column, but if you are inserting text to a field for each of the textbox columns and those correspond to a certian checkbox, you will be able to search your table for which textboxes have text for an entry and decide from that which boxes where checked, so you might even be able to just use the checkbox column as your identity (primary key). i.e., data structure like so:

Columns: checkbox / text1 / text2 / text3 / text4

Data: "some number" / "" / "some text" / "" / "some text"

If you were to query that data row above, you would be able to see that the check boxes in question are the ones without null text field rows. However, if you allow null text values you'd have to re-think this method a bit.

Anyway, the checkbox field can be whatever you want, and that will just depend on whatever logic you come up with. You could do it the long way and make a reference table that holds a unique value for each combination of textboxes, but this will get long and messy. Using 1 column to signify a checkbox group might not be the best way to do things, but that's not up to me since it's your data. Anyway, with that setup an insert query something like this might work for you:

"INSERT INTO check (checkBoxField,TextField1,TextField2,TextField3...)VALUES (" & "checkboxNumber" & "," & text1.text & ", " & text2.text & ", " & text3.text & ")"

..where *check* is the table name, and checkboxNumber is whatever you decide to put in the checkbox field.

inserting to multiple fields using a button

I have multiple textboxes in a page. How do i make them insert their values to multiple fields on multiple tables using a button.

You can just execute some SqlCommands to insert data to different tables in the Click event of a button, for example:

protected void Button2_Click(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection(@."Data Source=labsh96223\iori2000;Integrated Security=SSPI;Database=tempdb"))
{
conn.Open();
string insertSql = @."Insert into myTbl_1 select @.id, @.name";
SqlCommand myCommand = new SqlCommand(insertSql, conn);
myCommand.Parameters.Add("@.id", SqlDbType.Int);
myCommand.Parameters.Add("@.name", SqlDbType.VarChar, 100);
myCommand.Parameters["@.id"].Value = Int32.Parse(TextBox1.Text);
myCommand.Parameters["@.name"].Value = TextBox2.Text;
int i = myCommand.ExecuteNonQuery();
myCommand.CommandText = @."Insert into myTbl_2 select @.id, @.Description";
myCommand.Parameters.Clear();
myCommand.Parameters.Add("@.id", SqlDbType.Int);
myCommand.Parameters.Add("@.Description", SqlDbType.VarChar, 1000);
myCommand.Parameters["@.id"].Value = Int32.Parse(TextBox3.Text);
myCommand.Parameters["@.name"].Value = TextBox4.Text;
i= myCommand.ExecuteNonQuery();
//execute other commands
}
}

Inserting Text Into SQL 2005 Database Padded With Spaces

I have a Detailsview with Insert and Update options connected to a SQL 2005 table with templated textboxes for input. The textboxes have maxlength set to the number of characters in each respective field in the SQL 2005 table. When text is inserted it gets padded with spaces if all the field length is not used. When you try to edit the field the cursor does not move because of the padded spaces. The End key must be pressed to find the end of the string and the padded space removed before adding edited text. I am working in VB.net. If I check the field in SQL Studio Express is shows the text I typed plus blank space for the remainder of the field.

My question is how can I add text to the textboxes without the padded spaces being added when the maxlength of the field is not used?

if you look at the datatype of the data columns in the sql database, you will most likely find that it it set tochar.
the char datatype automatically pads with spaces to completely fill the allocated space.

If you prefer not to get these spaces, then you would want to use thevarchar datatype when setting up your table in sql server.
the varchar datatype will not add the padding.

Note: if you change the datatype of a column in an existing database, you will likely need to trim the existing data as it will still contain the padding. however, new data added to a varchar column will not get the padding.

|||

Thank you that worked like a charm. Three questions before I close this out:

1) How does the type nchar work? It comes up always as the default? Which type is the best?

2. Should I expect any suprises in my code where in some places I have embedded Rtrim statements?

3. When binding a variable to the table is there any format { } string that can be used to achieve the no padding result? I saw in a forum some place about the F parameter to reduce spaces but I did not know how to implement it?

|||

1) nchar is for storing fixed length unicode character data.
char will pad the data if created with ansi_padding set on. nchar always pads. http://msdn2.microsoft.com/en-us/library/ms175055.aspx
Which is best depends on your needs. As you've noticed, char/nchar might require to you to trim off whitespace.
For most text columns, I prefer varchar (or nvarchar for data i need to store as unicode).

2) Trimming the data with RTrim is unaffected by your use of char/varchar - other than it becomes unnecessary if your data is not padded. Note that the String type contains a TrimEnd method (among others) which i prefer over the trim methods from the visualbasic library.

3) I'm not aware of a standard format code that would trim the padding.

Here's an interesting thread:http://www.sqljunkies.com/Forums/ShowPost.aspx?PostID=7978

Inserting Question

Hey Guys:

I got a quick question, I am trying to insert five text boxes information into a sql database. I can easily do this by leaving the textboxes blank, and having a user enter a value then simply inserting the value into the database.

My issues is that I am creating a review page. The first page users will enter in values into a text box, then the second page(where the insert statement is located) displays the results, along with another query to find the user_id from the system.user. I keep getting an error stating that the viewstate is unknown, or something along those lines.

Basicly I think my error comes because I am passing the variables across the pages, then calling them, and then inserting them into the textbox values. I'm just wondering if you guys could tell me how to insert values from the past page, a tutorial would be great, or a site!

Oh: SqlServer, Asp.net, VB.net

Thanks,
Brian SierakowskiHeres the code I have before I do any of the INSERT statment stuff. Basicly I am going to put a button on the bottom of the page the when clicked will insert the Auction_id, User_id, Bid_date, Bid_amount, and Bid_status into the database.

Everything but the user_id is declared at the top, I then do a sql search to find the user_id based on the user.system.identity, and display it.

I am just completely lost here, I'm not sure If I need to create a dataset, or if I can just insert the information using an sql INSERT statement using the declared varables as the top, and the user_id, I may also be able to do a lookup for the user_is, and the insert in two sql statements, not sure.

Oh the table where the information will be submitted is simply a lookup table, the values are integers being inserted are related to other tables.

<%@. Page Language="vb" %>
<%@. Reference Page="placebid.aspx" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %>
<%@. Import Namespace="System.Web.Security" %
<script runat="server">
Dim fp As FirstPageClass
Dim sUser As String
Dim DateSubmitted As String
Dim AmountSubmitted As String
Dim StatusSubmitted As String
Sub Page_Load()
If Not IsPostBack Then
fp = CType(Context.Handler, FirstPageClass)
 End If
 sUser=Page.User.Identity.Name
 DateSubmitted = DateTime.Now
 AmountSubmitted = fp.Bid
 StatusSubmitted = 1
 Dim myDS As New DataSet()
 Dim ConnStr As String
 ConnStr = "CONNECTION STRING" 'changed by moderator due to presence of password -- thanks KraGiE
 Dim SQLSelect As String
 SQLSelect = "SELECT * FROM users WHERE users.username = '"& sUser &"' ;"
 Dim mySqlConn As New SqlConnection(ConnStr)
 Dim mySqlDA As New SqlDataAdapter(SQLSelect, ConnStr)
 mySqlDA.Fill(myDS)
 auction2.DataSource = myDS.Tables(0).DefaultView
 auction2.Databind()
End Sub
</script
<FORM runat="server" ID="Form1">
Hello <%=fp.UserName%> -- <%=fp.Bid%
<asp:datalist id="auction2" runat="server" Width="500" ShowFooter="False" ShowHeader="False" BorderWidth="0px" CellSpacing="5" repeatcolumns="1" gridlines="none" cellpadding="5">
 <ItemTemplate>
 <table cellspacing="1" cellpadding="0" bgcolor="#FFFFFF">
<tr>
<td bgcolor="#FFFFFF" class="maintext">
 Username:<%# DataBinder.Eval(Container.DataItem, "username")%>
 <br>
 User ID: <%# DataBinder.Eval(Container.DataItem, "user_id")%>
 <br>
</td>
</tr>
 </table>
 </Item Template>
</asp:datalist>
</FORM>

|||I don't really see what you're doing with fp = CType(Context.Handler, FirstPageClass), and that may be why it's not working. However, I was planning on emailing you, but I guess you don't want that due to it not being in your profile.

I'd delete that connection string as soon as you get this, and in the next post, just kindly leave a note starting with *** NOTE TO MODERATOR ***, and let them know the second post on this thread requires edits due to security purposes. Either way, I'd suggest changing the password as soon as you can.

inserting NULL into Datetime and int

Hello,

I have an asp.net page with different textboxes that put text into an sql database.
When I try to leave the textboxes blank that correspond with datatypes of int and datetime
in sql server it gives an error : I need to fill in these textboxes.

How can I solve this problem so I can leave the boxes blanc? What is the best sollution for this? I know I can set the types to String but then the user could fill in what he wants :)
Could you give me some advice on this please? All ideas are welcome.
Thanks !if you strictly want to enforce the user to enter the proper data and not mess up the system, use customvalidators for the textboxes.

for date, you can prbly have three textboxes for month, yr and day and validate it to numeric and when you add the values to db, concatenate them with a "/" in between.

or if the user leaves it blank, check if the txtDate.text = "" then use system.dbnull to enter null into the db.

HTH.|||Hey thanks ndinakar :)

Ok I've looked in the .net classes for this dbnull. There's no good example of how to do that.
Do I use this dbnull on the page script or in the database (for example in a stored procedure?)
Could you give an example please?(I'm very noob)

Thanks again for replying !