Addind Data to a SQL data table

Question

Afternoon everyone, I have been doing some research on how to use SQL databases and found a guide from microsoft. (https://learn.microsoft.com/en-us/visualstudio/data-tools/create-a-simple-data-application-by-using-adonet?view=vs-2022)

I have tried using this code and changing it to work for my purpose but it has not worked.

Here is the code below if anyone knows how to fix it, Thanks.

private void button1_Click(object sender, EventArgs e)
{
SqlConnection scn = new SqlConnection();
scn.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\LoginDB\logindb.mdf;Integrated Security=True;Connect Timeout=30";
SqlCommand scmd = new SqlCommand("select count (*) as cnt from LOGINDB where username=@HideakiUsui and password=@PWD ", scn);
scmd.Parameters.Clear();
scmd.Parameters.Add(new SqlParameter("@HideakiUsui ", SqlDbType.NChar));
SqlCommand.Parameters["@HideakiUsui "].Value = txt_UserName;
scmd.Parameters.Add(new SqlParameter("@PWD ", SqlDbType.NChar));

        }  

Answer 1

Your code is incomplete. It does not open a connection or execute the query. Also, the query does not Add records to the database. I'm a little confused as to what you are actually trying to accomplish.

Anyway, you'll want to use the ExecuteScalar() method since you're returning a single value; Count(*). Follow the coding pattern in the ADO.NET reference documentation.

SqlCommand.ExecuteScalar Method

Your code will look similar to the following where the username, password, and are passed to a method.

static public int Authenticate(string username, string password, string connString)  
{  
    Int32 count = 0;  
    string sql = "select count (*) as cnt from LOGINDB where username=@usr and password=@pwd";  
    using (SqlConnection conn = new SqlConnection(connString))  
    {  
        SqlCommand cmd = new SqlCommand(sql, conn);  
        cmd.Parameters.Add("@usr", SqlDbType.VarChar);  
        cmd.Parameters["@usr"].Value = username;  
          
        cmd.Parameters.Add("@pwd", SqlDbType.VarChar);  
        cmd.Parameters["@pwd"].Value = password;  
          
        try  
        {  
            conn.Open();  
            count = (Int32)cmd.ExecuteScalar();  
        }  
        catch (Exception ex)  
        {  
            Console.WriteLine(ex.Message);  
        }  
    }  
    return (int)count;  
}