how do I inner join two table to a dataset

Hello. I'm trying to inner join two tables into one dataset. Is that possible. If so I do I do it. I have my code down below, so if you could help me. please!! I'm using asp.net for my coding
[vb.asp.net-code]
Dim myConnection As New Data.Odbc.OdbcConnection(connectionstring)
Dim mysql As String = "select rmsfiles2.obcop200.line#, rmsfiles2.mspmp100.ptyp1, rmsfiles2.mspmp100.ptyp2, rmsfiles2.obcop200.quano, rmsfiles2.obcop200.quana, rmsfiles2.obcop200.quans, rmsfiles2.obcop200.c2rdt,rmsfiles2.mspmp100.prdno, rmsfiles2.obcop200.unitm, rmsfiles2.mspmp100.descp, rmsfiles2.obcop200.actsp, rmsfiles2.obcop200.ordno from rmsfiles2.obcop200 inner join rmsfiles2.mspmp100 on rmsfiles2.obcop200.prdno = rmsfiles2.mspmp100.prdno where rmsfiles.obcop200.ordno = " & Order.ToString()
Dim command As New Data.Odbc.OdbcCommand(mysql)
command.Connection = myConnection
Dim adapter As OdbcDataAdapter = New OdbcDataAdapter(command)
Dim ds As DataSet = New DataSet()
adapter.SelectCommand = New OdbcCommandselect rmsfiles2.obcop200.line#, rmsfiles2.mspmp100.ptyp1, rmsfiles2.mspmp100.ptyp2, rmsfiles2.obcop200.quano, rmsfiles2.obcop200.quana, rmsfiles2.obcop200.quans, rmsfiles2.obcop200.c2rdt,rmsfiles2.mspmp100.prdno, rmsfiles2.obcop200.unitm, rmsfiles2.mspmp100.descp, rmsfiles2.obcop200.actsp, rmsfiles2.obcop200.ordno from rmsfiles2.obcop200 inner join rmsfiles2.mspmp100 on rmsfiles2.obcop200.prdno = rmsfiles2.mspmp100.prdno where rmsfiles.obcop200.ordno = " & Order.ToString()
,myConnection)
adapter.Fill(ds, "rmsfiles2.OBCOP200, "MSPMP100")
gridview1.DataSource = ds
gridview1.DataBind()
[/code]While you can do it for selection and databinding purposes, I don't believe such a dataset will let you perform update/insert operations.|||I'm not going to update or insert. I'm just binding the two table together

Quote:

Originally Posted by davef

While you can do it for selection and databinding purposes, I don't believe such a dataset will let you perform update/insert operations.

sql

How do I handle time-zones?

I need to be able to consider time-zones, including daylight savings, within
stored procedures. Can anyone point me to a resource/code base which will
either allow me to procure or build my own code? I need to do things like
determine local time based on a customer's state, zip code, city, or whateve
r
is required to truly determine the customer's local time.
Thanks,
Michael"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:20421547-C406-4AA1-8E4A-FEF5012FCE5E@.microsoft.com...
>I need to be able to consider time-zones, including daylight savings,
>within
> stored procedures. Can anyone point me to a resource/code base which will
> either allow me to procure or build my own code? I need to do things like
> determine local time based on a customer's state, zip code, city, or
> whatever
> is required to truly determine the customer's local time.
> Thanks,
> Michael
You are in for a heap of work. Especially if you need to go international.
Just a couple of notes. Daylight savings time is done differently in
different countries. IIRC, the US currently switches on the last Sunday in
October and the first Sunday in April. England switches on the last Sunday
in October and the last Sunday in March.
Some countries require a minium of 150 days of daylight savings time, so
they have different schedules every year.
If you only need to do the US, then you will need to create a stack of
lookup tables for state, zip codes and cities. You will need logic to
default if the zip code or city doesn't exist. You will need to keep in
mind also that some states like Tennesse and Texas have multiple time zones.
You will need to keep in mind that Arizona and Hawaii don't do daylight
savings time and part of Indiana doesn't either.
Google for daylight savings time and you will find a number of articles
regarding this issue.
Good luck!
Rick Sawtell|||> Just a couple of notes. Daylight savings time is done differently in
> different countries. IIRC, the US currently switches on the last Sunday
> in October and the first Sunday in April.
And to add to the fun, that changes next year!
A|||I just posted this the other day for similar question here:
http://channel9.msdn.com/ShowPost.aspx?PostID=142586
Client would still need to pass it's standard time zone name to the proc or
you could have that data stored in a table by user.
William Stacey [MVP]
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:20421547-C406-4AA1-8E4A-FEF5012FCE5E@.microsoft.com...
>I need to be able to consider time-zones, including daylight savings,
>within
> stored procedures. Can anyone point me to a resource/code base which will
> either allow me to procure or build my own code? I need to do things like
> determine local time based on a customer's state, zip code, city, or
> whatever
> is required to truly determine the customer's local time.
> Thanks,
> Michael

How do i get the report in PDF format?

Hi all,

I need to get a report in PDF format.How to get that.I did the coding but am not getting.The following code i tried am not getting.what is the wrong in this coding.

WebMedRS.ReportingService rs = new WebMedRS.ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
parametersIn[0] = new ParameterValue();
parametersIn[0].Name = "claimid";
parametersIn[0].Value = Request.QueryString[0].ToString();
ParameterValue[] parametersOut = null;
byte[] result = null;
string reportPath = "/CMS/CmsFrom1";
string format = "Image";
string historyID = null;
string encoding;
string mimeType;
string extension;
Warning[] warnings = null;
string[] streamIDs = null;
result = rs.Render(reportPath, format, null, null, parametersIn, null, null, out encoding, out mimeType, out parametersOut, out warnings, out streamIDs);
Response.ClearContent();
Response.AppendHeader("content-length", result.Length.ToString());
Response.ContentType = "application/pdf";
Response.BinaryWrite(result);
Response.Flush();
Response.Close();

Thanks In Advance,

Yours,

Senthil

You will have to change the format:

string format = "PDF";

Jens K. Suessmeyer.

http://www.sqlserver2005.de

How do I get the initals of a name in a report?

Hello all,
does anybody know the code to get the name initials for a field in a report?
e.g. username is 'John Miller' and i want only the initials 'JM'?
Thanks.Hey is your data going to be same meaning (firstname lastname) then can be
done using
= left(field!name, 1) + " " + mid(field!name, instr(" ", field!name) +1 ,1)
left will return the first letter and instring will search the space +1 so
the last name letter is pointed and length is 1 so it picks up the first
letter of the lastname.
Amarnath
"Joggel" wrote:
> Hello all,
> does anybody know the code to get the name initials for a field in a report?
> e.g. username is 'John Miller' and i want only the initials 'JM'?
>
> Thanks.
>

How do I get the CLSID for SQL Server 2005 OLEDB Provider?

How do I get the CLSID for SQL Server 2005 OLEDB Provider? The following code yields the CLSID for 2000 Provider

m_hr = CLSIDFromProgID(L"SQLOLEDB",&clsid); // SQL Server

Thanks

JEK

Do you have in mind m_hr = CLSIDFromProgID(L"SQLNCLI",&clsid); ?

|||

That's it...

Thanks

JEK

How do I get SSIS to do this...?

I feel like I'm losing my mind. I can write code in 7 different languages, but I can't figure out how to create an SSIS package to do the following:

Table A, 6 columns: EmpID, Code1, Code2, Code3, LocationCode, ScriptPath

Table B, 5 columns: Code1, Code2, Code3, LocationCode, ScriptPath

Using the values in the Code columns for lookup, I need to pull the most specific value from Table B and store it in Table A. So the task is to fill in the LocationCode and ScriptPath columns in Table A from Table B using the following logic:

If

Table B has a row where Code1,

Code2 and Code3 all match the

appropriate values in the Table A

row then copy the LocationCode

from Table B to that row in Table A.

Else If

Table B has a row where Code1

and Code2 both match the appropriate

values in the Table A row then copy

the LocationCode from Table B to

that row in Table A.

Else If

Table B has a row where Code1

matches the appropriate value

Table A row then copy the

LocationCode from Table B to

that row in Table A.

Else

Place a default value in that row

in Table A.

Same logic for the ScriptPath.

The logic is pretty straight forward, but I can't wrap my brain around how to turn this into an SSIS package.

Can someone give me a quick sketch of what components to use to create something like this?

Thanks.

J

Roughly,

Lookup on 1, 2, 3

->Successful lookup -> Union All

->Failed lookup -> Lookup on 1, 2

->Successful lookup -> Union All

->Failed lookup -> Lookup on 1

->Successful lookup -> Union All

->Failed lookup -> Derived Column (for default) -> Union All

It's the same Union All for all branches.

Honestly, I think I'd do this in a Exec SQL, and not a data flow. If I am understanding your question properly, you will be updating Table A, so you'd either have to use an OLE DB Command in the data flow to issue the update, or write it to a temp table and issue an Exec SQL after the data flow.

|||

I'm writing it as a stored procedure now, but I just wanted to get my hands dirty with the SSIS stuff - so I gave it a shot.

I can't even figure out how to get the lookups to work properly in SSIS. When I play with it some more, I'll post the error that I'm getting. Maybe you can tell me what I'm doing wrong at that point...

Thanks for the response.

J

How do I find out what query someone ran?

Hello All,

We have an app that we do not have the source code for that is behaving badly. I'd like to find out what queries it is running in order to possibly fix the issue form the SQL server side of things. Anyone know what table/view I should select off of to find the queries that have been run recently?

Thanks in advance!

Kenny, your best bet is to fire up SQL Profiler and do some filtering so you only see the queries being executed by the application.

Thanks,
Sam Lester (MSFT)

How do I edit a query through code?

Hi!

Can someone tell me how or where I can find information on editind an SQL query through code?

I want to be able to run a user-defined lookup, where the user can choose what the query will look for.

Thanks in advance.

Guy

Your description is not very detailed. Do you want to create a dynamic query ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Hi!

Thanks for the advice.

I was in a rush when I posted that, but I can provide some more detail now.

I want to make a query, where the user can change what the search criteria is.

I want the user to enter a string literal value from a text box or drop-down list, and then view the query with that criteria.

This needs to be done through code, and ideally work in Visual Web Developer as well as Visual Basic.

Thanks.

|||

check this

http://www.sommarskog.se/dynamic_sql.html

Madhu

|||

Hi.

Thanks for that link.

I will have a look when I have time.

Guy