Problem wit sp_execute SQL

I have to make a large number of updates (about 29k) so I generated teh update statements into a table and am trying to sue sp_executesql to run them. Here is my code:

Declare @.SQLState NVARCHAR(500)

Declare Code Cursor
for
select SQLState from updates
open Code
FETCH NEXT FROM Code
into @.SQLState
While @.@.fetch_Status = 0
Begin
Exec sp_executesql @.SQLState

FETCH NEXT FROM Code
END

CLOSE Code
DEALLOCATE Code

IT appears to run succesfully, but the updates never happen - I get the following results for each update line:

UPDATE REEmployeeEvent SET UpdatedByEmployeeID= '00013' Where UpdatedByEmployeeID='00279'

(1 row(s) affected)

(0 row(s) affected)

Any ideas what I am doing wrong?

BTW - If I run the statements manually, they do work.

Thanks for any help!is there any particular reason why you want to use such a non-standard method to execute a script?

Why not execute dump them to a file, and execute the file as a single batch? At least that way you could visually inspect the scripts for correctness.

What you are trying to do here seems risky at best.

Problem while converting pdf document to Word.

Hi,
I have a pdf document which is generated from MS SQL Reporting Services.
I am unable to convert the pdf document(which was generated from MS SQL
Reporting Services) To a WORD document USINg SOme tools which i downloaded
from Internet.
The PDF document i generated using Reporting services,has just some static
text.
any idea?
thanks for your reply,
regards
praveenThis problem comes only with pdfs which are generated using MS SQL reporting
Services.
"praveen79" wrote:
> Hi,
> I have a pdf document which is generated from MS SQL Reporting Services.
> I am unable to convert the pdf document(which was generated from MS SQL
> Reporting Services) To a WORD document USINg SOme tools which i downloaded
> from Internet.
> The PDF document i generated using Reporting services,has just some static
> text.
> any idea?
> thanks for your reply,
> regards
> praveen

Problem when inserting to MS database

ok i've got several pages that insert with no problem but this one is giving me fits. The query it's generated works, because i've plugged it into the database through access and it worked. here is my code what am i doing wrong? It opens the connection and then gets to the executeNonQuery and doesn't do it not sure why.

Dim sqlInsert As String
 Dim ssn As String
 Dim ssnDash As String
 Dim mma As String
 Dim position As String
 Dim subject As String
 Dim mmaCheck As String
 Dim subjectCheck As String
 Dim bothCheck As String
 Dim otherCheck As String
 Dim flag As Boolean = False
 Dim added As Boolean = False
 Try
 If txtSSN.Text <> "" Then
 ssn = txtSSN.Text
 ssnDash = ssn.Substring(0, 3) & "-" & ssn.Substring(3, 2) & "-" & ssn.Substring(5, 4)
 End If
 mma = dlMMA.SelectedValue.ToString
 position = dlPosition.SelectedValue.ToString
 subject = dlMajor.SelectedValue.ToString
 Catch ex As Exception
 Response.Write("Please fill in all fields")
 End Try
 otherCheck = mma & subject
 Dim dr As DataRow
 For Each dr In dsMajor.Tables("List_Of_MMA").Rows
 If mma = dr.Item("mma").ToString And subject = dr.Item("Subject").ToString Then
 mmaCheck = dr.Item("MMA").ToString
 subjectCheck = dr.Item("Subject").ToString
 flag = True
 bothCheck = mmaCheck & subjectCheck
 End If
 Next
 If flag = False Then
 Response.Write("Didn't Work")
 Else
 sqlInsert = "INSERT INTO Student_Major(SSN, MMA, Subject, Position) Values('"
 sqlInsert += ssnDash & "','"
 sqlInsert += mmaCheck & "','"
 sqlInsert += subjectCheck & "','"
 sqlInsert += position & "')"
 Dim com As OleDbCommand = New OleDbCommand(sqlInsert, myConnection)
 Try
 myConnection.Open()
 com.ExecuteNonQuery()
 added = True
 Catch ex As Exception
 Response.Write(sqlInsert)
 Finally
 myConnection.Close()
 End Try
 'If added = True Then
 Response.Write("New Major, Minor, or Area added: " & subject)
 'End If
 End If

what is the xact error that you get ?|||i don't get an error it just will not execute the executeNonQuery command. I wish i got an error then i could fix it.|||Does it throw an exception? What's the message from exception handler??|||try doing a repsonse.write of your sqlInsert to see how its building up. also i'd recommend using parameterized queries..

hth|||Ok thanks for the replies we fixed the problem by changing the query to this

sqlInsert = "INSERT INTO Student_Major Values ('"
sqlInsert += ssnDash & "','"
sqlInsert += mma & "','"
sqlInsert += subject & "','"
sqlInsert += position & "')"

Where can i find information on parameterized queries?|||http://aspnet101.com/aspnet101/tutorials.aspx?id=1

Problem viewing report with Report Viewer

Hi,

I am using the Report Viewer Control to display the Reports. My query is generated dynamically so that output the query is a datatable. My application gives the user the flexibility to select the tables & the table-fields on which he wants to run the report. So I cannot have a fixed report file as I do not know what will the user select.

I tried adding the result datatable as the datasource for the report dynamically.

protected void RunReport_Click(object sender, EventArgs e){string constg ="server=localhost;Database=techapp;user id=sa;password=admin;";SqlConnection con =new SqlConnection(constg);//query is the query string that is generated at runtimeSqlDataAdapter da =new SqlDataAdapter(query,con);DataSet ds =new DataSet();da.Fill(ds,"ReportDataTable"); con.Close();ViewReport(ds.Tables["ReportDataTable"]);}protected void ViewReport(DataTable dt){ ReportViewer1.ProcessingMode = ProcessingMode.Local; ReportDataSource rd =new ReportDataSource(); rd.Name ="ReportData_Data"; rd.Value = dt; ReportViewer1.LocalReport.DataSources.Add(rd); ReportViewer1.DataBind();}

My problem is that the entire code gets executed properly without any exception but the report is not displayed. I checked the ReportDataSource & it shows the proper value. In all the samples that I went through, I found that they have mentioned a report file. I don't know if this is the cause of the problem, but if it is then In mycase how can I create a report design file if I wouldn't know what to add in that file at design time.

I do not have an expertise in this. can anyone please guide me through the right way & let me know what is it that I'm not doing correct?

I guess its the problem with height of report

Check whether you have set the height of report as 100%. Change that to some fixed size let say 800px.

Let me know if you need any further help

|||

Hi Ashok,

I tried adjusting the height of the report viewer . This is the changed code but it still doesn't show any thing.

 ReportViewer1.ProcessingMode = ProcessingMode.Local; ReportViewer1.Height = Unit.Pixel(500); ReportDataSource rd =new ReportDataSource(); rd.Name ="ReportData_Data"; rd.Value = ds.Tables["ReportDataTable"]; ReportViewer1.LocalReport.DataSources.Add(rd); ReportViewer1.DataBind();

Could the problem be the absence of a report design file ie an rdlc file. I downloaded a sample code from net where the report file is created dynamically & it seemed to work this way.

Is a report design file by default required for the report viewer control to display the report? What is the complexity involved in creating such design files on your own?? Can you shed some light on these points ?

Thanks