C
C#•3y ago
nevemlaci

Inserting into an SQL database from csharp

private void OrderClicked(object sender, System.EventArgs e)
        {
            
            string connetionString;
            connetionString = @"Data Source=sql7.freesql(...), (port);Initial Catalog=sql75(...);User ID=(...);Password=(...)";

            using (SqlConnection connection = new SqlConnection(connetionString))
            {
                connection.Open(); 
                connection.Close();
            }
        }
private void OrderClicked(object sender, System.EventArgs e)
        {
            
            string connetionString;
            connetionString = @"Data Source=sql7.freesql(...), (port);Initial Catalog=sql75(...);User ID=(...);Password=(...)";

            using (SqlConnection connection = new SqlConnection(connetionString))
            {
                connection.Open(); 
                connection.Close();
            }
        }
79 Replies
nevemlaci
nevemlaciOP•3y ago
I dotted out the credidentals but they should be ok Whenever I click the button to execute, I get this exception:
nevemlaci
nevemlaciOP•3y ago
nevemlaci
nevemlaciOP•3y ago
System.InvalidOperationException: 'Internal connection fatal error.' On the line 
connection.Open(); 
connection.Open();
*Any help is appreciated and I will read anything that will be there after my training sessions ❤️ * there is probably something about my connectionString I just can't figure it out
Yusef
Yusef•3y ago
Your connectionString has the wrong format
nevemlaci
nevemlaciOP•3y ago
can you please help me with it? 😅 the credidentals after the equal signs arent in parentheses it's just how i dotted it
Yusef
Yusef•3y ago
Here is an explanation on how to build it correctly with some examples
Yusef
Yusef•3y ago
nevemlaci
nevemlaciOP•3y ago
isnt that exactly my connection string tho?
nevemlaci
nevemlaciOP•3y ago
Yusef
Yusef•3y ago
BTW, wtf did you do?
nevemlaci
nevemlaciOP•3y ago
server and data source are the same thing
Yusef
Yusef•3y ago
nevemlaci
nevemlaciOP•3y ago
xdd thats just what i set
nevemlaci
nevemlaciOP•3y ago
Yusef
Yusef•3y ago
Ooh, 😂😂
nevemlaci
nevemlaciOP•3y ago
so back to the question, isnt my connectionString the same as the one u sent me? its just that my server(Data Source) has a port
Yusef
Yusef•3y ago
What does sql7.freesql(..) return?
nevemlaci
nevemlaciOP•3y ago
Thats what i used when i logged into myadmin
Yusef
Yusef•3y ago
Can you put a breakpoint before the using(... And check what's the value of connectionString
nevemlaci
nevemlaciOP•3y ago
I will, I'm in my car, I'll train an hour or so I gotta clear my head But yes of course I'll be able to do that
Yusef
Yusef•3y ago
Same lol Do you have the brackets around the sql7.freesql(...)?
nevemlaci
nevemlaciOP•3y ago
No Do I need it? It has no spaces in it
Yusef
Yusef•3y ago
If you are not putting the value directly, but getting from a method, it should be something like : connectionString = $"Datasource= {a variable or method}, {variable that contains the port number};... Is that what you have / are missing?
nevemlaci
nevemlaciOP•3y ago
I directly put the values, they aren't in variables Do you mean that the adress returns the value, so I need to treat it as a variable?
nevemlaci
nevemlaciOP•3y ago
nevemlaci
nevemlaciOP•3y ago
okay it is giving adifferent error but at least it is sql related now
Yusef
Yusef•3y ago
Try removing the brackets I thought sql7 was a class and freesql... a property of that class containing the actual data source
nevemlaci
nevemlaciOP•3y ago
i wrote a simple nodejs script and it works so its a syntax problem for sure
TheRanger
TheRanger•3y ago
freesql? never heard of it
nevemlaci
nevemlaciOP•3y ago
it makes you a 5mb mysql database im just tinkering with it it works with the node script 
const mysql = require("mysql2/promise")
const insert = async() =>{
    const cnn = await mysql.createConnection({
        host: "sql7.freesqldatabase.com" ,
        user: "(...)",
        password: "(...)",
        database: "(...)",
        port: 3306,
    })
    try{
        await cnn.query(
            "INSERT INTO orders (date, adress,pizza) VALUES ('1', '1', '1')" 
            
        )
        console.log("inserted");
    }
    catch(e){
        console.log(e);
    }
    
}
insert();
const mysql = require("mysql2/promise")
const insert = async() =>{
    const cnn = await mysql.createConnection({
        host: "sql7.freesqldatabase.com" ,
        user: "(...)",
        password: "(...)",
        database: "(...)",
        port: 3306,
    })
    try{
        await cnn.query(
            "INSERT INTO orders (date, adress,pizza) VALUES ('1', '1', '1')" 
            
        )
        console.log("inserted");
    }
    catch(e){
        console.log(e);
    }
    
}
insert();
(...) are credidentals dotted out but this nodejs script works fine it inserts the values so its just a syntax error somewhere ig
TheRanger
TheRanger•3y ago
ah so its mysql ?
nevemlaci
nevemlaciOP•3y ago
yes
TheRanger
TheRanger•3y ago
try this link https://www.connectionstrings.com/mysql/
MySQL connection strings - ConnectionStrings.com
Connection strings for MySQL. Connect using MySqlConnection, MySQLDriverCS, SevenObjects MySqlClient, Devarts MySqlConnection, MySQLProv.
nevemlaci
nevemlaciOP•3y ago
thanks System.ArgumentException: 'Keyword not supported: 'port'.'
TheRanger
TheRanger•3y ago
how did u write it?
nevemlaci
nevemlaciOP•3y ago
connetionString = @"Server=sql7.freesqldatabase.com;Port=3306;Database=3306;Uid=s...;Pwd=...;";
connetionString = @"Server=sql7.freesqldatabase.com;Port=3306;Database=3306;Uid=s...;Pwd=...;";
TheRanger
TheRanger•3y ago
password is visible here too lol
nevemlaci
nevemlaciOP•3y ago
idk why i do this xd like i care lol im just stupid af so
TheRanger
TheRanger•3y ago
well anyone can probably wipe ur database now
nevemlaci
nevemlaciOP•3y ago
the database with 0 data lul
TheRanger
TheRanger•3y ago
okay then maybe u need to put a space after each ; ?
nevemlaci
nevemlaciOP•3y ago
nope it doesnt like port it instantly throws the exception .
TheRanger
TheRanger•3y ago
u sure thats a mysql database?
nevemlaci
nevemlaciOP•3y ago
TheRanger
TheRanger•3y ago
try others u have plenty of connection strings to try in that link this one is related to MySqlDriverCS 
Location=myServerAddress;Data Source=myDataBase;User ID=myUsername;Password=myPassword;Port=3306;Extended Properties="""";
Location=myServerAddress;Data Source=myDataBase;User ID=myUsername;Password=myPassword;Port=3306;Extended Properties="""";
nevemlaci
nevemlaciOP•3y ago
nevemlaci
nevemlaciOP•3y ago
ive found this
TheRanger
TheRanger•3y ago
also what is Database 3306, is this how u called ur db?
nevemlaci
nevemlaciOP•3y ago
thats just a typo its fixed already
TheRanger
TheRanger•3y ago
thats for microsoft sql server
nevemlaci
nevemlaciOP•3y ago
ohh i got it working i had to uninstall the basic sql package because something conflicted with the mysql package
TheRanger
TheRanger•3y ago
ah shouldve asked you what exception it throws reading the exception message is very important
nevemlaci
nevemlaciOP•3y ago
i read it i was like wait there are multiple sql packages installed let me just remove one do i refer to variables with @ ? like @adress etc? okay it works but it said "value id cannot be null" the debugger shows it isnt null "MySql.Data.MySqlClient.MySqlException: 'Column 'date' cannot be null'"
nevemlaci
nevemlaciOP•3y ago
nevemlaci
nevemlaciOP•3y ago
and yet when i try to insert this id variable:
nevemlaci
nevemlaciOP•3y ago
nevemlaci
nevemlaciOP•3y ago
im trying to convert the datetime from c# into mysqls datetime format
TheRanger
TheRanger•3y ago
where are u trying to insert? yes, prefer to use parameterized queries
nevemlaci
nevemlaciOP•3y ago
well the debugger shows the values but only nulls get inserted
TheRanger
TheRanger•3y ago
well i dont know how ur code looks to judge
nevemlaci
nevemlaciOP•3y ago
cmd.Parameters exists tho ok im dumb i did everything OUTSIDE USING 
using (var connection = new MySqlConnection(connetionString))
            {
                
                
                connection.Open(); 
                using(var command= new MySqlCommand())
                {
                    DateTime now = DateTime.Now;
                    string id = now.ToString("yyyy-MM-dd HH:mm:ss");
                    string pizzaName = pizza.Text;
                    string adressName = adress.Text;
                    command.Parameters.AddWithValue("@idp", id);
                    command.Parameters.AddWithValue("@pizzaNamep", pizzaName);
                    command.Parameters.AddWithValue("@adressNamep", adressName);



                    command.Connection = connection;
                    command.CommandText = "INSERT INTO orders (date, adress, pizza) VALUES (@idp , @pizzaNamep, @adressNamep)";
                    command.ExecuteNonQuery();
                }
                connection.Close();
            }
using (var connection = new MySqlConnection(connetionString))
            {
                
                
                connection.Open(); 
                using(var command= new MySqlCommand())
                {
                    DateTime now = DateTime.Now;
                    string id = now.ToString("yyyy-MM-dd HH:mm:ss");
                    string pizzaName = pizza.Text;
                    string adressName = adress.Text;
                    command.Parameters.AddWithValue("@idp", id);
                    command.Parameters.AddWithValue("@pizzaNamep", pizzaName);
                    command.Parameters.AddWithValue("@adressNamep", adressName);



                    command.Connection = connection;
                    command.CommandText = "INSERT INTO orders (date, adress, pizza) VALUES (@idp , @pizzaNamep, @adressNamep)";
                    command.ExecuteNonQuery();
                }
                connection.Close();
            }
TheRanger
TheRanger•3y ago
u can pass a DateTime value to idp literally
nevemlaci
nevemlaciOP•3y ago
 command.Connection = connection;
                    command.CommandText = "SELECT MAX(id) FROM orders";
                    int maxid;
                    
                    maxid = command.ExecuteNonQuery();

                    int id = maxid++;
 command.Connection = connection;
                    command.CommandText = "SELECT MAX(id) FROM orders";
                    int maxid;
                    
                    maxid = command.ExecuteNonQuery();

                    int id = maxid++;
would this work? i have a nodejs script again to test if the sql is correct and it returns some weird thing, not only the integer but so much more
TheRanger
TheRanger•3y ago
if you looked at the microsoft docs of ExecuteNonQuery or if you hovered your mouse on the method it will tell u that this method returns the number of rows affected.
nevemlaci
nevemlaciOP•3y ago
huh okay
TheRanger
TheRanger•3y ago
ur looking for ExecuteScalar(); returns the first column of the first row in the resultset returned by the query ur select max would return only 1 row 1 column anyway
nevemlaci
nevemlaciOP•3y ago
maxid = (int)command.ExecuteScalar();
maxid = (int)command.ExecuteScalar();
like that?
TheRanger
TheRanger•3y ago
yeah
nevemlaci
nevemlaciOP•3y ago
thanks im just incremeting the id so i can have a key in the db
TheRanger
TheRanger•3y ago
why do that? just make the id column autoincrement and the database will increment it for you
nevemlaci
nevemlaciOP•3y ago
is there such thing?
TheRanger
TheRanger•3y ago
yes
nevemlaci
nevemlaciOP•3y ago
im glad we didnt learn anything about sql 😄
TheRanger
TheRanger•3y ago
glad? that sounds sad
nevemlaci
nevemlaciOP•3y ago
sarcasm
TheRanger
TheRanger•3y ago
lol
nevemlaci
nevemlaciOP•3y ago
can i do the auto increment thing on phpmyadmin?
TheRanger
TheRanger•3y ago
you probably can
nevemlaci
nevemlaciOP•3y ago
yeah i can how do i close a thread?

Did you find this page helpful?