Click here to Skip to main content
15,355,296 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Sir I am getting this error while creating custom connection with Sqlexpress in my class of desktop application.

What I have tried:

The Code in connect.cs:-
 public Connect(SqlConnection Sql,string connectingString) // it is the constructor
            {
                // Sql = null;
                using (Sql = new SqlConnection())
                {
                    int count = 0;
                    count = connectingString.Length;
                    char[] chr = new char[count];
                    chr = connectingString.ToCharArray();
                    string sd = new string(chr);
                    Sql.ConnectionString = sd.ToString();
                    Sql.Open();                    
                }                
            }
            public void Dispose()
            {
                this.Equals(null);
            }
            public static implicit operator string(Connect c)
            {
                throw new Exception(c.ToString());
            }
            public static implicit operator SqlConnection(Connect v)
            {
                throw new Exception("this is the cause : "+v.ToString());
            }

Now the code in Form1.cs:-

private void Form1_Load(object sender, EventArgs e) 
        { 
           SqlConnection conn=new SqlConnection();
            string commandText = "insert into admission values('CD002',9875613700)";
           // SqlCommand cmd = null;
            TableAdapter.Connect t = new TableAdapter.Connect(conn, "server=DESKTOP-4BVQVNQ;initial catalog=AMITAVA;user=sa;password=SQLEXPRESS");
           // MessageBox.Show(t.GetType().ToString());
            TableAdapter.Command cmdSql = new TableAdapter.Command(commandText,new SqlCommand());
            MessageBox.Show("Done");

        }
Posted
Updated 6-Apr-22 23:12pm

Um.
What the heck is all that about?
You are passed a connection string:
C#
public Connect(SqlConnection Sql,string connectingString)

You create a new SqlConnection object, overwriting the one that was passed in.
C#
using (Sql = new SqlConnection())

YOu find out how long it is - inefficiently:
C#
int count = 0;
count = connectingString.Length;

You create a new array of characters the same length:
C#
char[] chr = new char[count];

You throw it away, and convert the original string to an array:
C#
chr = connectingString.ToCharArray();

You then create a new string from the character array which is identical to the original string:
C#
string sd = new string(chr);

You then us the new, identical string to the SqlConnection object, after converting a string to ... a string:
C#
Sql.ConnectionString = sd.ToString();

You then exit the using block which Closes the connection and Disposes of it.
And then you exit the method, which throws away the only reference to the new (but deleted) SqlConnection object you created and opened ....

You do realize that is the same as this:
C#
public Connect(SqlConnection Sql,string connectingString) // it is the constructor
            {
            }
Except this version can't throw an exception for a bad SQL connection string...

Parameters in C# are passed by value not by reference: whatever you do within the method to a variable passed in by the caller is not reflected in external code.
So when you overwrite the value passed in as a parameter, the caller variable is not changed:
C#
private int Foo(int bar)
   {
   bar = bar * 2;
   }
...
int x = 666;
Foo(x);
Console.WriteLine(x);
Will always write "666".

So your external SqlConnection that you pass in doesn't get it's ConnectionString property set at all, and you get an error when you later try to open it.



Quote:
Sir you guided me well. i have written the modified code and the connection was successful. but regarding the command object there's a error. The error of initialisating the executeQuery() . I am supplying the code to see the code:-
C#
public Command(ref string commandText,ref SqlCommand cmd)
    {
    //TableAdapter.Connect TblCon;
    SqlConnection conn=new SqlConnection();
    
    cmd.CommandText = commandText;
    cmd.CommandType = CommandType.Text;
    string strConn = "";
    // TableAdapter TblAdp=new TableAdapter();
    // TblCon = new Connect(ref conn, ref strConn);
    
    // cmd.Connection = TblCon;
    if (cmd.CommandText.StartsWith("select"))
        {
        cmd.ExecuteReader();
        cmd.Dispose();
        }
    else
        {
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        }
    //TblCon.Dispose();
    }


You are just guessing, aren't you?
Why is the command string a ref parameter?
Why is the command object a ref parameter?

You don't modify either of them!

You create a connection, but never oper it or even use it, you create a SqlDataReader but throw it away - but that's OK because you Dispose the command it's associated with!

Stop guessing. Throw this whole load of rubbish in the bin, and sit down and think about what you are supposed to be doing, and what is a sensible way to do it. Because at the moment all you are doing is wasting your time typing this stuff as you clearly don't understand any part of what you are actually doing - and guesswork is a very, very slow way to get any project completed.

Go back to you course notes and read them again, then think about what you have been told. Don't bother to try and fix what you have so far, it's not worth saving!
   
v2
Comments
Member 12712527 7-Apr-22 7:13am
   
Sir you guided me well. i have written the modified code and the connection was successful. but regarding the command object there's a error. The error of initialisating the executeQuery() . I am supplying the code to see
the code:-

public Command(ref string commandText,ref SqlCommand cmd)
{
//TableAdapter.Connect TblCon;
SqlConnection conn=new SqlConnection();

cmd.CommandText = commandText;
cmd.CommandType = CommandType.Text;
string strConn = "";
// TableAdapter TblAdp=new TableAdapter();
// TblCon = new Connect(ref conn, ref strConn);

// cmd.Connection = TblCon;
if (cmd.CommandText.StartsWith("select"))
{
cmd.ExecuteReader();
cmd.Dispose();
}
else
{
cmd.ExecuteNonQuery();
cmd.Dispose();
}
//TblCon.Dispose();
}
OriginalGriff 7-Apr-22 7:42am
   
Answer updated.
Member 12712527 7-Apr-22 7:53am
   
Sir I have one problem i overlook the codes....It is ofcourse my fault that I did coding that way. If you permit me then (yes sir i was guessing). The ref keyword is used to keep reference of the object like pointers in C.
Member 12712527 7-Apr-22 7:59am
   
Sir you tell me that I haven't modified the code...the code goes like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.Sql;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Data.SqlClient;

namespace ReportBuilder.Net
{
public class TableAdapter
{
public class Connect
{
public Connect(ref OdbcConnection odbc,ref string connectingString)
{

odbc.ConnectionString = connectingString;
odbc.Open();

}
public Connect(ref OleDbConnection oledb,ref string connectingString)
{

oledb.ConnectionString = connectingString;
oledb.Open();

}
public Connect(ref SqlConnection Sql,ref string connectingString)
{
Sql.ConnectionString = connectingString;
Sql.Open();

}
public void Dispose()
{
this.Equals(null);
}
public static implicit operator string( Connect c)
{
throw new Exception(c.ToString());
}
public static implicit operator SqlConnection( Connect v)
{
throw new Exception("this is the cause : "+v.ToString());
}
}
public class Command
{
SqlConnection con=new SqlConnection();
public Command(ref string commandText,ref SqlCommand cmd,TableAdapter.Connect)
{
//TableAdapter.Connect TblCon;
SqlConnection conn=new SqlConnection();

cmd.CommandText = commandText;
cmd.CommandType = CommandType.Text;
string strConn = "";
// TableAdapter TblAdp=new TableAdapter();
// TblCon = new Connect(ref conn, ref strConn);

// cmd.Connection = TblCon;
if (cmd.CommandText.StartsWith("select"))
{
cmd.ExecuteReader();
cmd.Dispose();
}
else
{
cmd.ExecuteNonQuery();
cmd.Dispose();
}
//TblCon.Dispose();
}


public Command(ref string commandText,ref OleDbCommand cmd)
{
//TableAdapter.Connect TblCon;
OleDbConnection conn = new OleDbConnection();


cmd.CommandText = commandText;
cmd.CommandType = CommandType.Text;
string strConn = "";
//TblCon = new Connect(ref conn, ref strConn);
if (cmd.CommandText.StartsWith("select"))
{
cmd.ExecuteReader();
cmd.Dispose();
}
else
{
cmd.ExecuteNonQuery();
cmd.Dispose();
}

//TblCon.Dispose();
}

public Command(ref string commandText,ref OdbcCommand cmd)
{
// TableAdapter.Connect TblCon;
OdbcConnection conn=new OdbcConnection();
string strConn = "";
cmd.CommandText = commandText;
cmd.CommandType = CommandType.Text;
// TblCon = new Connect(ref conn, ref strConn);
if (cmd.Comma
Member 12712527 7-Apr-22 8:08am
   
i have done what so ever needed. but same error persists. if you correct me sir...
OriginalGriff 7-Apr-22 8:35am
   
As I said: throw that lot away and start again, without guesswork this time.
It is not worth your time to fix that code, it's just too poor to make it worthwhile!
That code is a complete mess! Throw it out and start again.
Quote:
C#
int count = 0;
count = connectingString.Length;
char[] chr = new char[count];
chr = connectingString.ToCharArray();
string sd = new string(chr);
Sql.ConnectionString = sd.ToString();
  1. You create a new array large enough to hold all of the characters in the string.
  2. You throw that array away, and convert the string to an array instead.
  3. You create a new string from that array.
  4. You call .ToString() on the string, which is pointless.

The string you end up with will be no different to the string you passed in. You have just wasted CPU cycles and memory to create a copy of the string.

Quote:
C#
using (Sql = new SqlConnection())
{
    ...
    Sql.ConnectionString = sd.ToString();
    Sql.Open();                    
}
  1. You overwrite the parameter value with a new SqlConnection instance.
  2. You set the connection string.
  3. You open the connection.
  4. Because you've wrapped it in a using block, you then dispose the connection before the method exits.
  5. Because you've passed the parameter by value, the original instance you passed in will not be modified. Its ConnectionString property has not been set, as the error tells you.


You've created all of this mess to obscure perfectly simple code:
C#
private void Form1_Load(object sender, EventArgs e) 
{
    using var conn = new SqlConnection("server=DESKTOP-4BVQVNQ;initial catalog=AMITAVA;user=sa;password=SQLEXPRESS");
    using var command = new SqlCommand("insert into admission (Col1, Col2) values (@Col1, @Col2)", conn);
    command.Parameters.AddWithValue("@Col1", "CD002");
    command.Parameters.AddWithValue("@Col2", 9875613700);
    conn.Open();
    int rows = command.ExecuteNonQuery();
    if (rows == 1)
    {
        MessageBox.Show("Done");
    }
    else
    {
        MessageBox.Show("Failed to insert row");
    }
}
   

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900