Can't INSERT INTO table

Question

0.00/5 (No votes)

See more:

I've been trying all day, but despite there being no error, I can't insert anything at all to my table. I don't know what is honestly wrong with the code, since the data I'm sending is correct, unless there is something missing in my sintax.

It just executes the code but won't insert the new row. The other functions in the form that use for example SELECT work just fine.

C#

public static string constr = @"Data Source=.\SQLEXPRESS;
                        AttachDbFilename=|DataDirectory|\cybercafe.mdf;
                        Integrated Security=True;User Instance=True;"; //in another form

public SqlConnection myConnection = new SqlConnection(MainContainer.constr);//catches the connection from the other form

string sqlIns = "INSERT INTO [Programs] (CodProgram, directory, logo ,description ,class ,type) VALUES (@codprogram,@directory,@logo,@description,@class,@type)";
myConnection.Open();
try
{
SqlCommand cmdIns = new SqlCommand(sqlIns, myConnection);
cmdIns.Parameters.AddWithValue("@codprogram",idprogram);
cmdIns.Parameters.AddWithValue("@directory", "'shortcuts\\" +txtname.Text +".lnk'");
cmdIns.Parameters.AddWithValue("@logo", "'icons\\" + txtname.Text + ".jpg'");
cmdIns.Parameters.AddWithValue("@description", txtdescription.Text);
cmdIns.Parameters.AddWithValue("@class", class);
cmdIns.Parameters.AddWithValue("@type", type);
cmdIns.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString()); 
}
myConnection.Close();

Thanks in advance guys.

Posted 4-Feb-13 2:37am

Steven Borges

Add a Solution

Comments

Irbaz Haider Hashmi 4-Feb-13 8:45am

I am not sure right now as it looks ok to me but few things you can test here.
[Programs] replace with dbo.[Programs]
Run the query with the data and see if there is any length issue with the parameters you are passing and the parameter length in the datatable

Steven Borges 4-Feb-13 8:51am

Got the same result, I also checked my table definition and compared it to the output I send to the table and it's indeed inside the boundaries of the size I specified.
Don't know why It doesn't work.

Fredrik Bornander 4-Feb-13 8:48am

What does the cmdIns.ExecuteNonQuery call return?

Steven Borges 4-Feb-13 8:54am

I don't know, how do I check that? I'm not very experienced with C# and Visual Studio.
I placed a breakpoint on it and it executes it, and also the rest of the entire code.

Fredrik Bornander 4-Feb-13 8:55am

Change it to
int rows = cmdIns.ExecuteNonQuery();
and inspect the value of that.

Steven Borges 4-Feb-13 8:58am

Made what you said and placed a breakpoint to check the value, it was 0.

Fredrik Bornander 4-Feb-13 9:00am

So no rows were updated, I'd try knocking up a sql statement and running it manually on the db to see it being inserted, then if that works, take that as is and use in code, and if that works split it up into a parameterized call like the one you have now.

Steven Borges 4-Feb-13 9:10am

Tried inserting it directly and didn't work either, now I'm just confused, INSERT INTO doesn't seem to work.

Fredrik Bornander 4-Feb-13 9:11am

Have your user got write access to the table/database?

Steven Borges 4-Feb-13 9:12am

Yes, I'm using the administrator account, and I also don't have any kind of user specification since I'm manipulating the .mdf file directly with queries instead of using the dataset.

Irbaz Haider Hashmi 4-Feb-13 8:55am

remove these

cmdIns.Parameters.AddWithValue("@codprogram",idprogram);
cmdIns.Parameters.AddWithValue("@directory", "'shortcuts\\" +txtname.Text +".lnk'");
cmdIns.Parameters.AddWithValue("@logo", "'icons\\" + txtname.Text + ".jpg'");
cmdIns.Parameters.AddWithValue("@description", txtdescription.Text);
cmdIns.Parameters.AddWithValue("@class", class);
cmdIns.Parameters.AddWithValue("@type", type);

and directly add parameters in the

string sqlIns = "INSERT INTO [Programs] (CodProgram, directory, logo ,description ,class ,type) VALUES (@codprogram,@directory,@logo,@description,@class,@type)";

run and if it is not inserted again. Try run the query generated by this directly on SQL.

phil.o 4-Feb-13 9:01am

Sorry but wrong : using parameters in SQL command and defining theses parameters' values are not the same thing.

Steven Borges 4-Feb-13 9:03am

Didn't work either using it directly.

Irbaz Haider Hashmi 4-Feb-13 9:07am

Any error you got?

Steven Borges 4-Feb-13 9:10am

Nope, everything got executed just fine.

Irbaz Haider Hashmi 4-Feb-13 9:11am

Can you paste the query generated by this here ?

fjdiewornncalwe 4-Feb-13 9:07am

Completely wrong. The way the parameters are being created and set is correct. My only hangup with the OP's code is the same as phil.o's observation of the single quotes (See solution 1)

Steven Borges 4-Feb-13 9:13am

Going to check it out now.

Irbaz Haider Hashmi 4-Feb-13 9:07am

string sqlIns = "INSERT INTO [Programs] (CodProgram, directory, logo ,description ,class ,type) VALUES ("+idprogram+",'"+"'shortcuts\\" +txtname.Text +".lnk','"+"'icons\\" + txtname.Text + ".jpg','"+txtdescription.Text+"','"+class+"','"+type+"')";
myConnection.Open();
try
{
SqlCommand cmdIns = new SqlCommand(sqlIns, myConnection);
cmdIns.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
myConnection.Close();

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Answer 1 · 2013-02-04T03:02:00

Solution 1

C#

cmdIns.Parameters.AddWithValue("@directory", "'shortcuts\\" +txtname.Text +".lnk'");

If I were you, I wouldn't include quotes (') in the beginning and end of the string (before shortcuts and after lnk).

Posted 4-Feb-13 3:02am

phil.o

Comments

Steven Borges 4-Feb-13 9:14am

But wouldn't that generate an error in the query?
I'm sending a string to the field afterall.

Steven Borges 4-Feb-13 9:16am

Tried and it actualy didn't generate any error, but it still gave me the same result.

phil.o 4-Feb-13 9:18am

Did you take out quotes in the following line also (@logo) ?

Steven Borges 4-Feb-13 9:22am

Yes, I removed it in both. No quotes in the entire code.
I was googling around and found this:
http://stackoverflow.com/questions/9382756/data-is-not-inserting-into-table

Could that be the same issue as me? If so I'm quite dissapointed since I don't realy want to create a connection in my machine, I want the .mdf file to be inside the application and to be accessible anytime I want by manipulating it's content.

phil.o 4-Feb-13 9:34am

It may be the same issue. I can't really help further because I never use user instances MSSQL databases (always access to the SQL service itself on the dedicated port).

Steven Borges 4-Feb-13 9:39am

I'm making this application so that I can use it on another systems with the same database, but as I read your comment it seems like I'm making it the wrong way. How do I make a database like you said so that multiple systems can access the same database and access the same data?

phil.o 4-Feb-13 9:48am

You just have to install SQL Server on your server and deploy an instance (default or not, it's up to you to decide). There is a free version of SQL Server (SQL Express) but it includes some limitations on the size of the databases (which depends on the version of SQL Express you are using).
Then you just have to configure SQL Service on the server so that it listens on TCP port 1433 (by default TCP connections are deactivated).

phil.o 4-Feb-13 9:17am

No, it won't. Parameters are here so that you don't have to bother with quotes.
Just try ;)