lauriemc

The following sql query works fine if I leave the four commented SET statements commented out. However, if I uncomment them, or just say I uncomment the first one SET @StoreID = tt_StoreID then I get the following message Invalid column name 'tt_StoreID' Why doesn't it recognize it, and how do I fix it ? *--------------------------------- DECLARE @txnTable table ( tt_StoreID int, tt_StoreName nvarchar (50), tt_BatchNumber int, tt_OpeningTime datetime, tt_ClosingTime datetime, tt_TransactionNumber int, tt_Price money, tt_Quantity float, tt_TenderID int, tt_TenderDesc nvarchar (25), tt_TEID int, tt_ItemID int, tt_ItemLookupCode nvarchar (25), tt_ItemDesc nvarchar (30) ) INSERT @txnTable SELECT Distinct dbo.Batch.StoreID, dbo.Store.Name, dbo.Batch.BatchNumber, dbo.Batch.OpeningTime, dbo.Batch.ClosingTime, dbo.TransactionEntry.TransactionNumber, dbo.TransactionEntry.Price, dbo.TransactionEntry.Quantity, dbo.view_TenderEntry_Distinct_TenderID.TenderID, dbo.view_TenderEntry_Distinct_TenderID.Description AS TenderDesc, dbo.TransactionEntry.ID AS TEID, dbo.Item.ID, dbo.Item.ItemLookupCode, dbo.Item.Description FROM dbo.Store INNER JOIN dbo.Batch ON dbo.Store.ID = dbo.Batch.StoreID INNER JOIN dbo.[Transaction] ON dbo.[Transaction].BatchNumber = Batch.BatchNumber AND dbo.[Transaction].StoreID = Batch.StoreID INNER JOIN dbo.TransactionEntry ON dbo.[Transaction].StoreID = dbo.TransactionEntry.StoreID AND dbo.[Transaction].TransactionNumber = dbo.TransactionEntry.TransactionNumber INNER JOIN dbo.view_TenderEntry_Distinct_TenderID ON dbo.Batch.StoreID = dbo.view_TenderEntry_Distinct_TenderID.StoreID AND dbo.Batch.BatchNumber = dbo.view_TenderEntry_Distinct_TenderID.BatchNumber AND dbo.TransactionEntry.TransactionNumber = dbo.view_TenderEntry_Distinct_TenderID.TransactionNumber INNER JOIN dbo.Item ON dbo.TransactionEntry.ItemID = dbo.Item.ID WHERE Batch.BatchNumber = 28613 ORDER BY Batch.StoreID, Batch.BatchNumber, TransactionEntry.TransactionNumber, Item.Description -- DECLARE @StoreID int DECLARE @BatchNumber int DECLARE @TransactionNo int DECLARE @ItemDesc nvarchar (30) DECLARE ttCursor CURSOR FOR SELECT tt_StoreID, tt_StoreName, tt_BatchNumber, tt_OpeningTime, tt_ClosingTime, tt_TransactionNumber, tt_Price, tt_Quantity, tt_TenderID, tt_TenderDesc, tt_TEID, tt_ItemID, tt_ItemLookupCode, tt_ItemDesc FROM @txnTable OPEN ttCursor FETCH NEXT from ttCursor -- The four lines below are where the errors occur -- SET @StoreID = tt_StoreID -- SET @BatchNumber = tt_BatchNumber -- SET @TransactionNo = tt_TransactionNumber -- SET @ItemDesc = tt_ItemDesc WHILE (@@FETCH_STATUS = 0) BEGIN FETCH NEXT from ttCursor END CLOSE ttCursor DEALLOCATE ttCursor SELECT * FROM @txnTable

The following is the code that uploades a bytearray into a file DSN on our mainframe. It works very well. What I want to do is upload the jcl which should then start to execute. That's the part I am stuck on. I used to be able to do it through WININET, but I want to get away from that and use the better FTP commands in vb.net Public Shared Sub UploadToMainFrame( _ ByVal ftpHost As String, _ ByVal ftpMainframeDSN As String, _ ByVal UserName As String, _ ByVal Password As String, _ ByVal DataToUpload As String) Dim ftpRequest As FtpWebRequest Dim ftpFullMainframePath = String.Format("ftp://{2}//'{3}'", ftpHost, ftpMainframeDSN) ftpRequest = WebRequest.Create(ftpFullMainframePath) ftpRequest.Credentials = New NetworkCredential(UserName, Password) ftpRequest.KeepAlive = True ftpRequest.UseBinary = False ftpRequest.Method = WebRequestMethods.Ftp.UploadFile ftpRequest. Dim byteArray() As Byte = StrToByteArray(DataToUpload) ftpRequest.ContentLength = byteArray.Length Dim ftpStream As Stream = ftpRequest.GetRequestStream() ftpStream.Write(byteArray, 0, byteArray.Length) ftpStream.Close() ftpStream = Nothing ftpRequest = Nothing End Sub

I am in the process of re-writing a vb.net windows application into a vb.net web application. I have a problem. In the vb.net windows app I used the DataGridTableStyle to successfully format a grid (datagrid) within code. The code looked something like this: Dim dgts1 As New DataGridTableStyle With dgts1 .MappingName = sptable End With Dim grdColumn1 As New DataGridTextBoxColumn With grdColumn1 .MappingName = "Unit_Number" ' mapping to SQL Server column name .HeaderText = "Unit" .Width = 45 End With dgts1.GridColumnStyles.Add(grdColumn1) And so on...... but in the web development environment, it is not recognizing the DataGridTableStyle. So my question is, what can I use in place of it, so that I can format my 'gridview' in code. Thank you, lauriemc

I'm working with vb.net 2005, Business Applications, a SQL Server report. In my report I have parameters that are multi-valued - this option can be set in the Report Parameters box. When the client goes to run the report, the drop down list for the multi-value parameter is a series of check boxes. I think this is pretty handy - except I can't get it to work in my stored procedure. If I pass in just one value, it works. If I pass in more than one value, it doesn't. The error message I get is 'Conversion failed when converting the nvarchar value '305,353' to data type int. This is my stored procedure: [highlight=sql] CREATE PROCEDURE [dbo].[spTenderReport] @Date_Begin datetime, @Date_End datetime, @STORENUM nvarchar(100), @Tender nvarchar(50) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT StoreID, [Name], BatchNumber, OpeningTime, ClosingTime, TransactionNumber, Price, Quantity, [iD], ItemLookupCode, Description, TenderID, TenderDesc FROM view_TenderReporting WHERE OpeningTime >= @Date_Begin AND ClosingTime <= @Date_End AND StoreID IN (@STORENUM) AND TenderID in (@Tender) END GO [/highlight] Right now I am only trying to send in the multi-values through @STORENUM, I haven't tried working with @Tender yet. Could someone please help. I admit I am not well-versed in complex SQL, which I have a feeling is what my code needs.......:confused:

The "Find" wants a sort previous to it. So now when I do it like this: DvBatch.Sort = Hold_Field DvBatch.Find(Hold_Search) what it seems to be doing is only sorting, but it points the column to the very beginning and then doesn't budge on subsequent key strokes.

I am using the following code to filter my datagrid's results. The batch number is entered in a text box and then on the keyup event, it fires off the following code: Dim Hold_Search As Integer Dim Hold_Field As String Hold_Search = Me.txt_BatchNumber.Text Hold_Field = "BatchNumber" DvBatch.RowFilter = Hold_Field & " = " & Hold_Search DvBatch.Sort = Hold_Field It does what it is supposed to do, but the problem is I really don't want the data 'filtered'. What i want is for the datagrid to scroll to the valid row and then keep scrolling based on the client's input until the desired value is reached - but without the filtering or winnowing out of the data. I hope this makes sense. Can anyone help me ? laurie mc

I am getting an error when I run a SQL Services Report - the report comes up fine, but then when I export it to pdf, it occasonally (not always) comes up with this error: � Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. (rrRenderingError) Get Online Help o Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown.  Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index It drives me crazy because it doesn't happen all the time. I have found some help on the web (most having to do with exporting to Excel, not to pdf), where it's suggested that one removes extra parens from their report expressions / formulas. I have done as much as I can, though I can't unparenthezize the Sum(Fields!.....), those parentheses seem to be built into the SUM function. Anybody else have this problem ? Something else I can offer is that it is a multi-parameter report - not as in different parameters, but many items can be entered on the one parameter. We built a store procedure to take care of that: it looks like this: CREATE PROCEDURE [dbo].[batchNumberReport] @BatchNumber varchar(500) AS DECLARE @BatchNo nvarchar(500) , @sql as varchar(2000) set @BatchNo = dbo.CreateBatchString(@BatchNumber) Set @sql = 'SELECT ITEM, SOURCE, TETOTAL, DEPT, CATEGORY, BatchNumber, Status,QTY FROM view_Batch4k WHERE CAST(BatchNumber as varchar) IN (' + @BatchNo + ')' exec(@sql) GO The @BatchNo never, ever exceeds 500 characters, so I am really lost as to why the exception is being thrown. Help, please laurie mc

We did this in the database itself, and while it seems to prevent the log file from growing to gigantic proportions, it doesn't prevent it from growing. I did find the kind of tool I wanted, which I put in a stored procedure. The sp code is simply this: DBCC SHRINKDATABASE (dbname, 10) GO I call it from the program before and after the upload / download, and it works like a charm, shrinks that log file right down. Thanks, laurie mc

A little background on my problem: we have a pilot program in the gov't agency where I work where we are testing a vb.net Windows Application with a LOCAL SQL server database. The people who go out on the road do not have any inter/intranet connection, once they are on the road they have static data. It is used primarily for inquiry purposes, some printing. They also do not have SQL Enterprise or any SQL management tools. The data is refreshed from our mainframe computer in the morning, when they plug into the intranet through their shop. We don't do a backup on the individual laptop, rather we keep a backup on another computer here in the main office. From that computer, if our roving laptop is plugged into the intranet, we can shrink the logfile. I am finding that the size of logfile seems to impact the length of time it takes to download the necessary data from the mainframe. It is a clean wipe, btw, each table is truncated, then reloaded. My question is this: is there any way to programatically shrink that log file from the vb Windows application? I would like to see that done before the download begins, and without our road people having to call me and ask that it be done. Thank you, laurie mc

I want to open a specific Access application from a Windows vb.net application so that I can access reports. I have gotten as far as Dim AccessApp as New Access.Application But don�t know how to point to the .mdb I want to, nor open it. Can somebody please show me how ? It�s probably not that hard but I just spent the whole day building a print form in vb.net, only to find out, even going in the bitmap direction as suggested by Microsoft, that one can no longer print forms from VB. And in this particular app, SQL Server Reports is not an option� Thank you, lauriemc :mad:

I have a datagrid in vb.net. I have used the DataGridTableStyle class to format it successfully - until now. I don't know if what I am doing should be in the DataGridTableStyle, perhaps somehow I should be formatting the grid itself. Several of the columns in my grid are true/false. What I want to do is if the display / contents are 'true', then I want to set the fore color to green, otherwise I want to leave it black. Has anyone used the DataGridTableStyle to successfully do this ? Please share, thank you, laurie mc

Your first three lines were the most important lines, as I was missing 2 of them. Thanks:o

I am trying to do an Export to Excel using vb.net in a Windows Application, AND, I realize there are many good threads already here on the topic. BUt I have a problem. I have added the Excel reference from COM, the Excel 11 Object Library, I can see it in my Solution Explorer, but I am still getting an error on my line: Dim excelApp As New excel.application with the wavy error line under excel.application It's like it's not recognizing the reference. I have also tried an Imports and Inherits just for the heck of it, but still am not having any luck. Help! I need to break through this first step.... :(

Thanks everybody :) The last solution worked wonderful - ONCE I realized I had an .htm file instead of an .html file. I would have gone around in circles for days if I hadn't finally tried testing with a mypage... right on the C drive - and then realized my extension was wrong. But i learned some things and am happy about that, thanks again

No, it didn't. This is the code I used: pr = pr.Start("IEXPLORE", "file://c:/TU_Help/index.html") and it came back with a message saying: Cannot find 'file:///c:/TU_Help/index.html'. Make sure the path or Internet Address is correct. What I noticed is that it put an extra slash in front of the c: I did check the address and it checks out okay.:(