SQL Query - Database Discussion Boards

hi Paddy
Thanks for your reply

i have add the "@" before the variable as you said, now i get another Error

Procedure 'Insert_Client'excepts parameter '@inClientName',which was not supplied

Thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

hi all
i wount to know if
the sql server 2005 Developer Edition Souport the SQlServer authentecation

thanks for any body hellp me Rose | [Rose]

Thaer

Yes it does.

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

how can i download backup of messagese as a file

Hi,
My aim is to insert a image in to a picture box from the access database
As we know that access database stores images in the bitmap format with Ole object as the datatype.
To do this task i am having two codes

byte []b ;
b = (byte[]) bincomm.ExecuteScalar();
if (b.Length > 0)
{
MemoryStream stream = new MemoryStream(b, true);
stream.Write(b, 0, b.Length);
$$ Image i = Image.FromStream(stream);
pbox.BackgroundImage = i;

}

bindcon.Close();

for this code a exception is thrown at line $$ saying invalid parameter .........
if anybody knows how to do this please let me know as i have strucked at this point
regards
sindhu tiwari

Always Innovative

i have checked the contents in the tables for one of the tables in msaccess
what i found was that the first record of the table was filled with the values as [select].
can somebody tell me why it appeared that way?

Hello, im trying to create a simple web service that allows a user to insert data (from a form) into a DBtable. Here is my webMethod:
-----------------------------------
<webmethod()> _
Public Sub SubmitRatingsForCourse(ByRef dataSet As DataSet)
Using connection As New SqlConnection("ConnectionStrings:RatingsConnection")

Dim adapter As New SqlDataAdapter()
adapter.SelectCommand = New SqlCommand("SELECT ID, CourseID, Rating, Review, ReviewID, DateStamp FROM RatingsTable", connection)

Dim builder As SqlCommandBuilder = New SqlCommandBuilder(adapter)
connection.Open()

Dim dSet As DataSet = New DataSet
adapter.Fill(dSet, "RatingsTable")
Dim INSstr As String
INSstr = "INSERT INTO [RatingsTable] ([ID], [CourseID], [Rating], [Review], [ReviewID], [DateStamp]) VALUES ('@ID', '@CourseID', '@Rating', '@Review', '@ReviewID', '@DateStamp')"

builder.GetUpdateCommand()

adapter.Update(dSet, "RatingsTable")
End Using
End Sub
----------------------------------------

Now when I try to create the clientSide that consumes the service I:
- Launch Visual Web Developer 05 Express.
- Create the WebReference
- Drag a detailsView onto the page
- Create an ObjectDataSource
- Choose my BusinessObject and click next
- Then for the *Select* I choose GetRatingsForCourse() Returns DataSet
- Then in the *insert* tab I choose SubmitRatingsForCourseAsync(DataSet dataSet)
Then when I go to test, the page comes up, the form fields are there, I insert some test data and submit and here is the error:
-------------------------
Could not find a property named 'ID' on the type specified by the DataObjectTypeName property in ObjectDataSource 'ObjectDataSource1'.
-------------------------

Where am I going wrong? Please help...

-- modified at 15:52 Wednesday 7th November, 2007

-- modified at 15:54 Wednesday 7th November, 2007

-swo

I have three tables.

Modules - contains one or more software modules that the user can subscribe to
Subscribed - contains subscription info for a module that is subscribed to by users
Membership - contains user membership info

The idea is to return all modules whether they are subscribed to by the user or not, but for the subscribed ones, I need all the data from the subscribed and membership tables as well. For modules that are not subscribed to by the user, the columns that came from the subscribed and membership tables would have null values.

More info on the tables:

The Modules table contains 1 or more rows, with one row indicating a unique module (no dupes).

The Membership table contains 1 or more rows, with each row indicating a unique user (no dupes)

userName   UserID  MoreData
john       1       dog
mary       2       cat
bob        3       bear
martha     4       monkey
sally      5       rat

The subscribed table contains 0 or more rows, and there could be multiple rows for a given user (but never more than one row per subscribed module for that user, like so:

<code>userID    moduleID
1         1
1         2
2         1
3         3
3         4
4         3
5         1
5         2
5         3</code>

So, if there were four modules, and I was interested in john's subscription status, I would want this outcome (remember I want all modules in the dataset, but with john's subscription status on the subscribed ones):

<code>ModuleID   ModuleName    UserID   SubscribeDate   LastUpdate  MoreData    UserName
1          Blah          1        1/2/2007        1/2/2007    dog         john
2          Gack          1        2/3/2007        2/3/2007    dog         john
3          Shoot         NULL     NULL            NULL        NULL        NULL
4          Darn          NULL     NULL            NULL        NULL        NULL</code>

I kept the number of columns to a dull roar for the sake of sanity, but it's safe to assume I want all columns from all three tables when appropriate.

Can someone help me out?

"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001

Which part of the query are you having trouble with?

It looks like you need to do a LEFT JOIN between Module and Subscribed and a LEFT JOIN between Subscribed and Membership, fetching whichever fields are required.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Off the top of my head, this should do it:

select mod.ModuleId, ModuleName,
UserId, SubscribeDate, LastUpdate,
MoreData, UserName
From Subscribed s
Left Join 
Membership m
On m.userId = s.userId
Left Join
mod.ModuleId = s.ModuleId

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

I think he meant to have Module as the main table.

SELECT
    m.ModuleID,
    m.ModuleName,
    s.UserID,
    s.SubscribeDate,
    s.LastUpdate,
    u.UserName,
    u.MoreData
FROM
    Modules m
LEFT JOIN
    Subscribed s
    ON (m.moduleID = s.moduleID)
LEFT JOIN
    Membership u
    ON (s.UserId = u.UserId
ORDER BY
    ModuleName

Oh. OK then. Nice catch.

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

Smells like homework anyway... Laugh | :laugh:

Michael Potter wrote:
Smells like homework anyway...

Naaaaaaw.

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

I'm 51. I haven't done homework since 1974.

"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001

Open mouth, insert keyboard - I am wrong again. Sorry about the assumption.

I ended up writing almost 140 lines of SQL code (using a cursor - what a pain in the ass) to accomplish my goal, but I'm still going to see if I can do it with a select statement, so I'll use Mr Potter's version of your suggestion.

"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001

Does anyone know if it's possible to control the T-SQL output from LINQ to SQL in any way? Specifically, I'd like to control the definition of the parameters created for sp_executesql when using variable-length data types (varchar, decimal, numeric).

VS 2008 defines the data types correctly by declaring the size when I drag and drop a table into the designer (O/R mapper?) for my dbml file. But that value isn't considered when the sp_executesql statement is generated. So the size of the gernated parameter is the size of the value passed in (ex. "test@test.com" becomes nvarchar(13) regardless of the size of the column in the database).

Thanks,
Mark

Ok, I've found one way to do this but it's hardly elegant and it doesn't allow you to take advantage of delayed execution:

DataContext.GetCommand returns System.Data.Common.DbCommand which allows you to access the Parameters collection where you can set the size. If you're using SQL Server you need to set Precision you'll have to cast the parameter to a SqlParameter.

After you've updated the parameter you can call ExecuteReader (don't forget to make sure the connection is open). Then use the DataContext.Translate<tresult(dbdatareader) method="" to="" convert="" the="" datareader="" ienumerable<t="">.

The problem is this doesn't allow for delayed execution because you can't update the actual command used by the query variable.

Just as an update, I've found a way to inject my workaround (read: hack) into the process in a way that is transparent to the consumer of my class. I'm going to create a class which wraps System.Data.Linq.Table<t> and create a custom implementation of the GetEnumerator() and GetEnumerator<t>() methods which will parse the expression tree (haven't proved this part of the concept yet) and determine the parameter mappings, then I'll be able to reflect over T (I'll have to cache the result of this for performance) to determine the correct value for DbParameter.Size.

When I've completed the ability to parse the expression tree I'll write an article on everything I've learned. Wish me luck!

Mark

Please how can I insert a table from excel file into an sqlserver table programmatically using C#.That is create a table in sqlserver with a file in excel that already have the table defined.Using c#.

By searching Google[^].

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

I want to query a table for the previous date.

Not sure how to construct the WHERE statement.

Thanks,

The answer was;

WHERE [TimeStamp]>= Convert(varchar(10), DateADD(DAY, -1, GETDATE()), 101) AND [TimeStamp] < Convert(varchar(10), GETDATE(), 101)

This gave the info for the previous day only.

Brian

Hi all,

I have the following script to do a back up of SQL2005 database:

BACKUP DATABASE [Repo] TO DISK = N'C:\Program Files\repo.dat' WITH NOFORMAT, NOINIT, NAME = N'repor-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

When i try to look for Repo.dat file under C:\programfiles i cant get it.

Please advice

Thanks