Re: How to add a table to a database of an SQLServer (in C#) - Database Discussion Boards

Re: SQL and Progress Updates

Dave Kreskowiak22-May-07 12:01

Dave Kreskowiak

22-May-07 12:01

This isn't the complete code to do the upload.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Re: SQL and Progress Updates

Expert Coming22-May-07 20:02

Expert Coming

22-May-07 20:02

			string szSQLCommand1;<br />
            string szSQLCommand2;<br />
			Int32 intFileSize;<br />
			byte[] rawData;<br />
			FileStream fsRead;<br />
<br />
			try<br />
			{<br />
				fsRead = new FileStream(lblPath.Text, FileMode.Open, FileAccess.Read);<br />
				intFileSize = Convert.ToInt32(fsRead.Length);<br />
<br />
				rawData = new byte[intFileSize];<br />
				fsRead.Read(rawData, 0, intFileSize);<br />
				fsRead.Close();<br />
<br />
                mySQLConnection.Open();<br />
                <br />
                szSQLCommand1 = "INSERT INTO Files(Name, Size) VALUES(@Name, @Size)";<br />
                szSQLCommand2 = "INSERT INTO FileData(Data) VALUES(@Data)";<br />
<br />
				cmd.Connection = mySQLConnection;<br />
				cmd.CommandText = szSQLCommand1;<br />
				cmd.Parameters.AddWithValue("@Name", txtName.Text);<br />
                cmd.Parameters.AddWithValue("@Size", intFileSize);<br />
<br />
                cmd2.Connection = mySQLConnection;<br />
                cmd2.CommandText = szSQLCommand2;<br />
                cmd2.Parameters.AddWithValue("@Data", rawData);<br />
<br />
				cmd.ExecuteNonQuery();<br />
                cmd2.ExecuteNonQuery();<br />
<br />
				MessageBox.Show("File Inserted into database successfully!", "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);<br />
<br />
				mySQLConnection.Close();

The only way to speed up a Macintosh computer is at 9.8 m/sec/sec.

Re: SQL and Progress Updates

Dave Kreskowiak23-May-07 15:39

Dave Kreskowiak

23-May-07 15:39

There is no way for this code to give you a progress update. This code will block on both ExecuteNonQuery statements. Neither can give you any kind of progress data, not the UI thread time to show it.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Calling SQL updates from Command Prompt

Pualee21-May-07 11:41

Pualee

21-May-07 11:41

Hey,

I am wondering if it is possible, and what would be the best way?

I'm working on a batch process to take care of building our applications, push/pull from source control and update the databases. As far as updating the DB, there will be a few scripts that need to run and I would like to call them from the same batch process that updates our executables (I really don't want to run a batch file AND set up a SQL job).

The goal is to have a single (1) job which will take care of everything to reduce the chance of error.

Can someone point me in the right direction?

Thanks,
Pualee

Re: Calling SQL updates from Command Prompt

mr_lasseter22-May-07 7:18

mr_lasseter

22-May-07 7:18

For oracle you can use sqlplus calls, for Sql Server you can use iSql.

Mike Lasseter

Re: Calling SQL updates from Command Prompt

Pualee23-May-07 2:37

Pualee

23-May-07 2:37

Thanks for the jumping off point, I'm looking into this, more specifically oSQL now Smile | :)

How to add a table to a database of an SQLServer (in C#)

Leia201121-May-07 4:27

Leia2011

21-May-07 4:27

Hey guys,

I need to know how to add a table to a database in SQL Server 2000.
With C# code its possible to change tables, i.e to update or delete rows or whatever, but how can I create a new table? Is this possible? do you have any ideas?

thanks in advance

Re: How to add a table to a database of an SQLServer (in C#)

Colin Angus Mackay21-May-07 5:22

Colin Angus Mackay

21-May-07 5:22

Leia2011 wrote:
how can I create a new table?

You can put pretty much any SQL command you like through an SqlCommand object. If you don't expect a result set back use ExecuteNonQuery()

Leia2011 wrote:
Is this possible?

yes.

Leia2011 wrote:
do you have any ideas?

See above.

Upcoming events:
* Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Run Sql jobs in sequence

Aaanand21-May-07 4:21

Aaanand

21-May-07 4:21

Hi
I have created one master job in sqlserver 2000 which run several jobs in different steps.

But it excutes all jobs at a time. I want that job run one by one.
I have set property like "Go TO Next in one finish successfully" but it didnt work in this case.

If i run several store procedures in one job then they run one by one as i need but when i put jobs inside job then they run simultaneously.

I also tried by putting each job in seprate store procedure and kept that store procedures in each steps but it also didnt work and run simultaneously.

I can not understand how to do it.
Could anybody give me the solution?

Thanks in advance.

Re: Run Sql jobs in sequence

andyharman21-May-07 8:09

andyharman

21-May-07 8:09

Jobs are meant to be independent of each other. How about using moving your code into a DTS package - with "success" constraints linking each task? The master job would then just need to execute that one DTS package.

SQL 2005 Report Services

Jonathan Snyder21-May-07 4:13

Jonathan Snyder

21-May-07 4:13

SOftware I'm using

Visual Studio 2005
SQL Express 2005
SQL Server Business Intelligence Studio

My Problem:

I created two reports and using the same parameter for each report. I add report2 to report 1 as a subreport and whenever I preview report1, report2 never shows as the subreport. I doubled, tripled check that they are using the same parameter but I read in another article that you need to pass a report parameter and not a query parameter? What's the difference? Help would be greatly appreciated.

Thanks
Jon

index

WhiteGirl2321-May-07 2:50

WhiteGirl23

21-May-07 2:50

Hi,
I need to find all the indexes in a table from access database.
please help me .
thank you.

Re: Finding indexes on an MS-Access table

andyharman21-May-07 3:25

andyharman

21-May-07 3:25

You could try the ADOX or DAO.

How to insert a binary file into SQL database, via ADO

lilnelse21-May-07 2:16

lilnelse

21-May-07 2:16

I want to insert a binary file, say "tt.bin", into a field (image type) of SQL database, I wrote a program as follows

C++, VS2003, ADO, input file: tt.bin，database: moldb,
Create table moltbl (id INT, mol IMAGE);
But I didn't get nothing in the output file: new.bin

where am I wrong, please? thanks very much!

// BLOB.cpp

#import "c:/Program Files/Common Files/System/ADO/msado15.dll" \
no_namespace rename("EOF", "EndOfFile")

#include
#include

using namespace std;

int main()
{
::CoInitialize(NULL);

_RecordsetPtr m_pRecordset("ADODB.Recordset");
_ConnectionPtr m_pConnection("ADODB.Connection");
_bstr_t bstrSQL("select * from moltbl");

ifstream moldata;
moldata.open("tt.bin", ios::in|ios::binary);
ofstream newdata;
newdata.open("new.bin", ios::out|ios::binary);

char buffer[10240];
char *pBuf = buffer;
long nFileLen = 0;

if (!moldata.fail() && !moldata.eof())
moldata.read(buffer,10240);
nFileLen = moldata.gcount();

VARIANT varBLOB;
SAFEARRAY *psa;
SAFEARRAYBOUND rgsabound[1];
int index = 0;
_bstr_t strConnect = "Provider=SQLOLEDB; Server=ELSE-HP;"
"Database=moldb; uid=sa; pwd=sa";
m_pConnection->Open(strConnect, "", "", adModeUnknown);
if(m_pConnection == NULL)
cerr<<"Load data ERROR!\n";
HRESULT hr = m_pRecordset->Open("SELECT * FROM moltbl",
_variant_t((IDispatch *)m_pConnection,true),
adOpenDynamic,
adLockPessimistic,adCmdText);
m_pRecordset->AddNew();

m_pRecordset->PutCollect("id",_variant_t(index++));

if(pBuf)
{
rgsabound[0].lLbound = 0;
rgsabound[0].cElements = nFileLen;
psa = SafeArrayCreate(VT_UI1, 1, rgsabound);
for (long i = 0; i < (long)nFileLen; i++)

SafeArrayPutElement (psa, &i, pBuf++);
varBLOB.vt = VT_ARRAY | VT_UI1;
varBLOB.parray = psa;
m_pRecordset->GetFields()->GetItem("mol")->AppendChunk(varBLOB);
}

m_pRecordset->Update();

long lDataSize =_pRecordset->GetFields()->GetItem("mol")->ActualSize;

ifstream newfile;
newfile.open("new.bin", ios::out| ios::binary);
if(lDataSize > 0)
{
_variant_t varBLOB;
varBLOB = m_pRecordset->GetFields()->GetItem("mol")->GetChunk(lDataSize);

if(varBLOB.vt == (VT_ARRAY | VT_UI1))
{

SafeArrayAccessData(varBLOB.parray,(void **)&pBuf);

newdata.write(pBuf, lDataSize);
SafeArrayUnaccessData (varBLOB.parray);
}
}

moldata.close();
newdata.close();
return 0;
}

Hi, there. Need your help!

Hi, there.

Re: How to insert a binary file into SQL database, via ADO

Sathesh Sakthivel21-May-07 2:37

Sathesh Sakthivel

21-May-07 2:37

Hey what error it shows?

Regards,

Satips.

Re: How to insert a binary file into SQL database, via ADO

lilnelse21-May-07 2:57

lilnelse

21-May-07 2:57

Thanks for your attention!

No error, everything seems OK, but I couldn't get the right result,
I insert the binary file into the database, and then I retrieve and output it into a binary file, but I got nothing in the new file?

what's the matter? thanks a lot!

Hi, there.

Re: How to insert a binary file into SQL database, via ADO

lilnelse21-May-07 3:05

lilnelse

21-May-07 3:05

Sorry, but it's already 9:00 PM in my time-zone, I have to leave now, I'll try to fix my program tonight, but if you could give me any suggestions, that'll be much appreciated.

PS. I'm just a beginning learner, so please explain in details. Thanks. See you.

Hi, there.

Re: How to insert a binary file into SQL database, via ADO

lilnelse21-May-07 17:02

lilnelse

21-May-07 17:02

I've stepped into it, and find something seems wrong here:

if(pBuf)
{
rgsabound[0].lLbound = 0;
rgsabound[0].cElements = nFileLen;
psa = SafeArrayCreate(VT_UI1, 1, rgsabound);
for (long i = 0; i < nFileLen; i++)
SafeArrayPutElement (psa, &i, pBuf++);
varBLOB.vt = VT_ARRAY | VT_UI1;
varBLOB.parray = psa;
m_pRecordset->GetFields()->GetItem("mol")->AppendChunk(varBLOB);
}

because when i did as follow:

m_pRecordset->Update();
long lDataSize = m_pRecordset->GetFields()->GetItem("mol")->ActualSize;

I find that the value of lDataSize is alwayse -1
do you know the reason, please?

thanks a lot

regards.
Li

Hi, there.

Optimizing crystal report

Pradip Kishore21-May-07 1:36

Pradip Kishore

21-May-07 1:36

hi all
i am having a stored procedure in my database which will take 2 parameters and return a resultset which i want to display in a crystal report.when i am executing the store proc. alone with the paramaters in the query analyser of sql server 2000,it is taking around 2.5 min.but when i pass the parameters from a vb.net page page,it z showing an error:{"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."}

please help me out to solve this problm..

thanks in advance

i am Pradip Kishore

Re: Optimizing crystal report

kubben21-May-07 2:32

kubben

21-May-07 2:32

The command timeout defaults to like 30 seconds, so you would need to change this so you don't get a timeout error. There is a property off of the sqlCommand object called CommandTimeout. It is in seconds.

Hope that helps.
Ben

Re: Optimizing crystal report

Paul Conrad21-May-07 5:14

Paul Conrad

21-May-07 5:14

Have you looked at optimizing the stored procedure?

Re: Optimizing crystal report

Pradip Kishore21-May-07 22:04

Pradip Kishore

21-May-07 22:04

hi
thanks for your response

IS there any way to optimize a stored procedure for quick data retrival..

please help me

thanks in advance..

i m pradip kishore

Re: Optimizing crystal report

Paul Conrad22-May-07 5:37

Paul Conrad

22-May-07 5:37

Pradip Kishore wrote:
IS there any way to optimize a stored procedure for quick data retrival..

In short, you need to look at where bottlenecks are. Without having any idea how your database is set up, I can't really help.

Any queries you have in the stored proc, analyze it through query analyzer...

Paul

Re: Optimizing crystal report

Dave Kreskowiak21-May-07 6:45

Dave Kreskowiak

21-May-07 6:45

Pradip Kishore wrote:
but when i pass the parameters from a vb.net page page,it z showing an error:{"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."}

The default CommandTimeout for an SqlCommand object is 30 seconds. If your query doesn't return a result set in that time, you get this error.

Either you have to optimize the stored procedure and/or table structures (indexes!), and/or up the timeout in the SqlCommand object.

If it's taking 2.5 minutes to return a set, are you sure that this is as fast as your query can execute?

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Re: Optimizing crystal report

Pradip Kishore21-May-07 22:10

Pradip Kishore

21-May-07 22:10

Hi
thanks for your response..

but can you show me some sample code or any links by which i can be able to do this..?

thanks in advance..

pradip kishore

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.