Database

Re: SQL Query Issue: MSSQL vs. Access

2-May-08 3:33

You are likely having problems with parameter order in the query. Unlike the SQl Data classes, Oledb essentially ignores parameter names, and relies exclusively on parameter position in the query. It seems likely that you might be depending on matching the parameter names (rather than their order in the query string) before executing the query (although I can't tell that from the code provided...).

PDTUM2-May-08 6:02

PDTUM

2-May-08 6:02

Rob,

Thank you for your thoughtful answer. Yes, It has been so long since I used OLEDB that I forgot that, but in this case, it was not the problem. I mentioned in the beginning that I was suspicious that it had something to do with the syntax passing the parameter. I found an obscure piece of code on the net yesterday that showed me the problem. It had to do with passing the single quotes. The solution is pasted below in the event that it might also help some others:

//For Access Query
string Sql1 = "SELECT DISTINCT T.Trans_Name AS Transmission, [M.Manufacturer] AS Manufacturer, [M.Concern] AS Concern, " +
"[M.Years] AS Years, [M.Description] AS Description, [M.ATB] AS Bulletin " +
" FROM Master AS M INNER JOIN Trans AS T ON M.Trans = T.Trans WHERE ";

string Sql2 = " AND ";
string trans = textBoxTrans.Text;
string Sql3 = " T.Trans_Name = \'" + trans + " \'";

Thanks to you and "Youtea" for your input, Pat

Re: SQL Query Issue: MSSQL vs. Access

Mike Dimmick2-May-08 6:02

Mike Dimmick

2-May-08 6:02

Access does not support named parameters (e.g. @Manufacturer). It only supports positional parameters indicated with the ? character.

DoEvents: Generating unexpected recursion since 1991

How to calculate count?

GroCareer30-Apr-08 17:25

GroCareer

30-Apr-08 17:25

Hi

My Sql query is returing below resultset.

SNO Id Count
1 101
2 102
3 104
4 102
5 106
6 102
7 104

I want if Id is repeating like 102 and 104 is repeating in above resulset .it should display count as:

SNO Id Count
1 101 1
2 102 1
3 104 1
4 102 2
5 106 1
6 102 3
7 104 2

Please help how i write query for calculating clount value.

Its urgent
Plz help asap.
Thanks

Re: How to calculate count?

Harini N K30-Apr-08 18:58

Harini N K

30-Apr-08 18:58

Hi

Try this:

SELECT <br />
	SNO,<br />
	Id,<br />
(<br />
	SELECT <br />
		(COUNT(*) + 1) <br />
	FROM <br />
		table1 b <br />
	WHERE <br />
		b.SNO < a.SNO AND <br />
		a.Id = b.Id)<br />
FROM <br />
	table1 a<br />

<br />
SELECT <br />
	SNO,<br />
	Id,<br />
(<br />
	SELECT <br />
		COUNT(*)<br />
	FROM <br />
		table1 b <br />
	WHERE <br />
		b.SNO <= a.SNO AND <br />
		a.Id = b.Id)<br />
FROM <br />
	table1 a<br />

Harini

Database upload and Append

L²+D30-Apr-08 3:24

30-Apr-08 3:24

Hi, I am trying to upload a database (which I have no problem doing), then I need to take that database and append it to a master database with the same tables and structure. I am using Access and visual studio .net, C#. I have tried everything, I am new to C# and .net and so maybe I am not putting things in the correct order or something?? Specifically, I have to use two different connections to get to the data, one connection where the data is located (conn1), and then append that data to the master database, (conn2). Please help if you can! Mad | :mad:

L²+D

Rob Graham30-Apr-08 11:42

30-Apr-08 11:42

Please suppy at least enough code snippets for someone else to understand what you've tried, and which part is not working. Your present question is sufficiently vague that no one is likely to respond.

L²+D1-May-08 3:21

1-May-08 3:21

Hi Rob,
as I said, I am new to this so here is what I have done;

<br />
private bool uploadData(string myPath, string txtUpload)<br />
		{<br />
			bool bNoError = true;<br />
			string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +<br />
				"Data Source=" +myPath +txtUpload +";";<br />
<br />
			OleDbConnection con = new OleDbConnection(strConnection);<br />
			con.Open();<br />
			<br />
			<br />
			try<br />
			{<br />
				DataSet dsEWEtmp = new DataSet();<br />
				<br />
				OleDbDataAdapter daEWEForm = new OleDbDataAdapter("SELECT * FROM Some_Form", con);<br />
<br />
				<br />
				int updates = daEWEForm.Fill(dsEWEtmp, "Some_Form");<br />
				foreach (DataRow dataRow in dsEWEtmp.Tables)<br />
				{<br />
					dsEWEtmp.Tables[0].ImportRow(dataRow);<br />
					dsEWEtmp.Tables[0].AcceptChanges();<br />
				}<br />
<br />
				updates = daEWEForm.Update(dsEWEtmp);<br />
//				return dsEWEtmp;<br />
<br />
				DataTable tblEWEForm;<br />
				tblEWEForm = dsEWEtmp.Tables[0];<br />
			<br />
				OleDbCommandBuilder objCommandBuilder = new OleDbCommandBuilder(daEWEForm);<br />
				daEWEForm.Update(dsEWEtmp, "Some_Form");<br />
			}<br />
catch(Exception err)<br />
			{<br />
				string str = err.Message;<br />
				bNoError = false;<br />
					<br />
			}<br />
			finally<br />
			{<br />
				if (con.State.ToString() =="Open")<br />
				{<br />
					con.Close();<br />
				}<br />
			}<br />
			return bNoError;

As I stated I have the file that I upload; then if there is an uploaded file, it is "supposed" to read that file into a dataset and write it to the master database. In my web.config file, the master database resides at strConnection2, the uploaded file is at strConnection. I am really confused how to access them both, one to get the data from the uploaded file and two connect to the master database and write to it.

Is that better? Please help if you can. Thanks! Confused | :confused:

L²+D

Youtea1-May-08 16:36

Youtea

1-May-08 16:36

You grab your data from strConnection and stored it in the DataSet dsEWEtmp. Now go ahead and open up strConnection2 and insert the data. I'm pretty sure you can find how to insert from dataset very easily from this site.

Youtea2-May-08 5:40

Youtea

2-May-08 5:40

Wow, I can't believe someone gave me 1 point because I didn't put the codes up. Isn't this a C# question anyways?

L²+D2-May-08 6:41

2-May-08 6:41

It probably is a C# question, but as I said, I am new to all this and for me; it has to do with databases as well as the code behind. I don't know who gave you one point, but a little code for pointing in the right direction would have helped. Thanks. Sniff | :^)

L²+D

Rob Graham2-May-08 3:21

2-May-08 3:21

First, you need two different connections, one for the input database, the other for the output. If the data in the "upload" copy is just to be appended to the "master" copy (no changes to existing rows in the master), then I would open a data reader on the "upload" table, and use an isert query to insert rows to the master while reading rows from the updload - psuedocode:

ConnMaster.Open(masterconnstring);
ConUpload.Open(uploadConnString);
string sql = "insert into master_table (f1,f2,f3) values (?,?,?)"
OledbCommand cmmd = new OleDbCommand(ConnMaster,sql);
using (Datareader dr = ConnUpload.Executereader("select f1,f2,f3from upload table");)
{
  while (dr.Read())
  {
    try{
    cmd.parameters.clear();
    cmd.parameters.add(new oledbparameter("p1",dr[0].getvalue());
    cmd.parameters.add("p2",dr[1].getvalue());
    cmd.parameters.add("p3",dr[2].getvalue());
    cmd.execute();
    }
    catch(oledbexception e)
    {
      logdata("insert failed" + e.tostring());
     }
  }
}

code not exact, please look up correct syntax .

L²+D2-May-08 5:00

2-May-08 5:00

Thanks for your help Rob, but I am not exactly sure where this code would go; after the dset is defined or ??? Also, the insert into (f1,f2,f3) values (?,?,?)" I want to copy all columns, can I put insert into (*)" and leave off values?

Sorry to be such a pain, but like I said, I am REALLY new to this and still shakey on my feet (code Blush | :O

)

Thank you again for being such a help to such a bother Big Grin | :-D

L²+D

Rob Graham2-May-08 7:31

2-May-08 7:31

L wrote:
can I put insert into (*)" and leave off values?

No, you need to explicitly name the columns (since these are different connections and different databases, * wont work (and is bad practice in any case). Also, the number of columns must match the number of values in the values clause. The order of the parameters must match the field name order.

You don't need the dataset at all. It's not helping you. The datareader is a fast replacement for the dataset in this case, it represents the results from the query as a collection of rows that can be read once only in forward order.

L²+D6-May-08 11:14

6-May-08 11:14

Rob,
the following is my code...still having problems. Could you please take a look? Sigh | :sigh:

<br />
                        <small>OleDbConnection con = new OleDbConnection(strConnection);<br />
			<br />
			OleDbConnection con2 = new OleDbConnection(strConnection2);<br />
			<br />
			con.Open();<br />
			con2.Open();<br />
			string strSQL = "INSERT INTO First_Table([OID]) values (@OID)";<br />
			string strSQL2 = "Select [OID] From First_Table";<br />
                        OleDbCommand cmd2 = new OleDbCommand(strSQL2,con2);<br />
			<br />
			OleDbCommand cmd = new OleDbCommand(strSQL,con);<br />
			<br />
			OleDbDataReader dr = cmd.ExecuteReader();<br />
			<br />
		<br />
			while (dr.Read())<br />
			{<br />
				try<br />
				{<br />
					<br />
				cmd.Parameters.Add (new OleDbParameter("OID",dr[0].ToString()));<br />
cmd.ExecuteReader();<br />
				}					<br />
				catch (Exception err)<br />
				{				<br />
					string str=err.Message;<br />
					bNoError=false;<br />
					<br />
				}					<br />
				finally<br />
				{<br />
					if (con.State.ToString() == "Open")<br />
						con.Close();<br />
					if (con2.State.ToString() == "Open")<br />
						con2.Close();<br />
				}</small><br />

L²+D

Rob Graham12-May-08 12:33

MySql Connector .NET table exists

12-May-08 12:33

You need to clear your parameters collection after executing the cmd. Also, don't use executereader here (the insert command), you don't need it and it can only cause problems, use cmd.execute instead. Even if executereader was a typo, continually adding the same parameter (even with a different value) to the parameters collection will cause errors.

annodomini230-Apr-08 2:56

annodomini2

30-Apr-08 2:56

Hi all,

Possibly my first post, if not certainly for a while.

I have a simple C# program which is creating a table in a MySQL db, I want to know how to find out if the table exists already before creating the table. Had a look on the net and here, nothing really answers my question.

Can anyone help?

FYI: Running, VS2005, MySql 5.0.41 and .NET Framework 2

Thanks
AD2

Do not disturb, I'm disturbed enough as it is!

Pete O'Hanlon30-Apr-08 3:27

Pete O'Hanlon

30-Apr-08 3:27

Try IF NOT EXISTS `Schema`.`Table` to test the table.

Deja View - the feeling that you've seen this post before.

My blog | My articles

N a v a n e e t h30-Apr-08 3:34

N a v a n e e t h

30-Apr-08 3:34

This is the table creation syntax

CREATE TABLE IF NOT EXISTS YourTableName
    (create_definition,...)
    [table_option] ...

Is this what you are looking for ?

All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems.- Unclyclopedia

How to use google | Ask smart questions

annodomini230-Apr-08 4:54

annodomini2

30-Apr-08 4:54

I'm aware of this capability, however there is potential for the table structure to change and if it exists there was problem when the function ran previously and I would want to delete the table.

The program is being used to update a MySQL Db from an Access db

Is IF NOT EXISTS an instruction in its own right?

Do not disturb, I'm disturbed enough as it is!