SQL insert not working in my code

Question

I have a SQL Insert in my code behind and when I click the button, it seems to update the Textbox control on my page and not my database. Here's the code behind:

protected void UpdatePic(object sender, EventArgs e)
{
    string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\TravelJoansDB.mdb;";
    string cmdstr = "INSERT INTO BlogEntryItems(Picture) VALUES (@UpdatedPic)";

    OleDbConnection con = new OleDbConnection(constr);
    OleDbCommand com = new OleDbCommand(cmdstr, con);

    TextBox uPic = (TextBox)DataList1.Items[1].FindControl("BEIPictureField");

    con.Open();
    com.Parameters.Add("@UpdatedPic", uPic.Text);
    com.ExecuteNonQuery();
    con.Close();
}

The ASP code is for a button where the OnClick event calls this method:

<asp:DataList ID="DataList1" runat="server" DataSourceID="AccessDataSource1">
<ItemStyle />
<ItemTemplate>
    <table>
        <tr>
            <td>
                <br />
                <asp:Image ID="Image1" CssClass="placePicCenter" runat="server" 
                BorderWidth="1px"
                BorderColor="#EEEEEE"
                ImageUrl='<%# "PlaceImages/" + Eval("Picture") %>' /><br />
                <asp:TextBox ID="BEIPictureField" runat="server" Text='<%# Bind("Picture") %>' /><br />
                <asp:Button ID="UpdatePicButton" runat="server" Text="Update" OnClick="UpdatePic" />
                <br />
            </td>
        </tr>
        <tr>
            <td>
                <asp:Label ID="Label4" CssClass="placeBodyStyle" runat="server" Text='<%# Eval("PicText1") %>' />
            </td>
        </tr>
        <tr>
            <td>
                &nbsp;
            </td>
        </tr>
    </table>
</ItemTemplate>
</asp:DataList>

If you debug this in visual studio, does the ExecuteNonQuery() run? Also, how are you checking your database? Sometimes that's the problem. — Dan Bracuk
– Dan Bracuk, Commented Oct 3, 2013 at 1:42
How do I check what value is returned from the ExecuteNonQuery command? I check my database by opening it up and then opening up the table. It is an Access database. — Joseph
– Joseph, Commented Oct 3, 2013 at 2:21
ExecuteNonQuery returns an integer telling you how many rows were effected, which in your code you do not capture; store the value in a variable e.g. var x = com.ExecuteNonQuery(); and either print it somewhere or check it in the debugger. — Preston Guillot
– Preston Guillot, Commented Oct 3, 2013 at 2:51
I'm tyring to use the System.Console.Writeline(), but it doesn't seem to show anything when I trigger the call for my function. — Joseph
– Joseph, Commented Oct 3, 2013 at 3:21

George Stocker · Accepted Answer · 2013-10-03 02:14:20Z

1

There could be multiple reasons this is happening, without more information from you, this is just a shot in the dark.

Make sure you have write permissions to the database.
Try modifying your connection string to using either computer name or IP address instead of |DataDirectory| to connect.

edited Oct 3, 2013 at 2:14

George Stocker

58k29 gold badges184 silver badges239 bronze badges

answered Oct 3, 2013 at 1:57

campagnolo_1

2,7501 gold badge20 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

MikeSmithDev Over a year ago

This should be posted as a comment, not an answer.

Dan Bracuk Over a year ago

The person who posted it does not have a high enough reputation to post comments.

campagnolo_1 Over a year ago

That is correct, Dan. Sorry if it doesn't pass your muster, Mike, but it's currently the only way for me to try and help other users.

George Stocker Over a year ago

Your best bet to pass community muster is to rephrase this as an answer where this is a list of things to try.

campagnolo_1 Over a year ago

Thank you for the advice, George!

|

Black Frog · Accepted Answer · 2013-10-03 03:39:08Z

0

What is the underlining column data type? Because depending on the data type for Microsoft Access, the blob/binary have different size.

According to Data Type Support (OLE DB):

BigBinary DBTYPE_BYTES 4,000 bytes
LongBinary DBTYPE_BYTES 1,073,741,823 bytes
VarBinary DBTYPE_BYTES 510 bytes

Also, why don't you use the SQL Server Express with ASP.Net? It's free.

The command string to attached a SQL Express file is easy:

string constr = @"Data Source=.\SQLEXPRESS;AttachDbFileName=e:\data\Customers.mdf;Integrated Security=True;User Instance=True";

answered Oct 3, 2013 at 3:39

Black Frog

11.7k1 gold badge37 silver badges67 bronze badges

1 Comment

Joseph Over a year ago

Believe you me, next time I will be using SSE. The underlying data type is a string.

Collectives™ on Stack Overflow

SQL insert not working in my code

2 Answers 2

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related