Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#
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.
 
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
Comments
Irbaz Haider Hashmi at 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 at 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 at 4-Feb-13 8:48am
   
What does the cmdIns.ExecuteNonQuery call return?
Steven Borges at 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 at 4-Feb-13 8:55am
   
Change it to
int rows = cmdIns.ExecuteNonQuery();
and inspect the value of that.
Steven Borges at 4-Feb-13 8:58am
   
Made what you said and placed a breakpoint to check the value, it was 0.
Fredrik Bornander at 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 at 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 at 4-Feb-13 9:11am
   
Have your user got write access to the table/database?
Steven Borges at 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 at 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 at 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 at 4-Feb-13 9:03am
   
Didn't work either using it directly.
Irbaz Haider Hashmi at 4-Feb-13 9:07am
   
Any error you got?
Steven Borges at 4-Feb-13 9:10am
   
Nope, everything got executed just fine.
Irbaz Haider Hashmi at 4-Feb-13 9:11am
   
Can you paste the query generated by this here ?
Marcus Kramer at 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 at 4-Feb-13 9:13am
   
Going to check it out now.
Irbaz Haider Hashmi at 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

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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).
  Permalink  
Comments
Steven Borges at 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 at 4-Feb-13 9:16am
   
Tried and it actualy didn't generate any error, but it still gave me the same result.
phil.o at 4-Feb-13 9:18am
   
Did you take out quotes in the following line also (@logo) ?
Steven Borges at 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 at 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 at 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 at 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 at 4-Feb-13 9:17am
   
No, it won't. Parameters are here so that you don't have to bother with quotes.
Just try ;)

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 525
1 CPallini 480
2 George Jonsson 353
3 OriginalGriff 260
4 Pheonyx 218
0 OriginalGriff 4,813
1 CPallini 3,890
2 Sergey Alexandrovich Kryukov 3,274
3 George Jonsson 2,552
4 Gihan Liyanage 2,186


Advertise | Privacy | Mobile
Web04 | 2.8.140905.1 | Last Updated 4 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100